CODEWITHSUNDEEP
oracle-database
neel.1708
neel.1708

Reputation: 315

Fetch single record from duplicate rows from oracle table

I have a table user_audit_records_tbl which has multiple rows for a single user ,Every time user logs in one entry is made into this table so i want a select query which will fetch a latest single record for each user, I have a query which uses IN clause.

Table Name : user_audit_records_tbl

Record_id Number Primary Key,
user_id varchar Primary Key ,
user_ip varchar,
.
.
etc

Current query i am using is

select * from user_audit_records_tbl where record_id in (select max(record_id) from user_audit_records_tbl group by user_id);

but was just wondering if anybody has better solution for this since this table has huge volumns.

Upvotes: 1

Views: 605

Answers (2)

Arkadiusz Łukasiewicz
Arkadiusz Łukasiewicz

Reputation: 6346

select * 
from ( select row_number() over ( partition by user_id order by record_id desc) row_nr,
              a.*
       from user_audit_records_tbl  a 
     ) 
where row_nr = 1
;

Upvotes: 1

Steven
Steven

Reputation: 15258

You can use the first/last function 

select max(Record_id) as Record_id,
       user_id, 
       max(user_ip) keep (dense_rank last order by record_id) as user_ip, 
       ...
from user_audit_records_tbl 
group by user_id

No sure if it will be more efficient.

EDIT : As above query is less efficient, may be you could try an exist clause

select * 
from user_audit_records_tbl A
where exists    (    select 1
                    from user_audit_records_tbl B
                    where A.user_id = B.user_id
                    group by B.user_id
                    having max(B.record_id) = A.record_id
                )

But maybe, you should look on the index side instead of the query side.

Upvotes: 1

Related Questions