CODEWITHSUNDEEP
databaseoracle11gdistinctanalytic-functions
christopher alex
christopher alex

Reputation: 11

how to use distinct on multiple columns

I have 6 fields

f1,f2,f3,f4,f5,f6

only fields 4 to 6 only vary i want result as single row based on field 1

Eg

name ,  age,    policy_no,  proposer_code,      entry_date ,        status
-----------------------------------------------------------------------------
aaa     18        100002       101              20-06-2016              A
aaa     18        100002       101              21-06-2016              B  
aaa     18        100002       101              22-06-2016              c   
aaa     18        100002       101              24-06-2016              H
aaa     18        100002       101              26-06-2016              p

I want the last row alone only based on proposer code because that is the most recent entry date.

Upvotes: 0

Views: 39

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269803

If I understand correctly, you just want to use row_number() like this:

select t.*
from (select t.*,
             row_number() over (partition by name order by entry_date desc) as seqnum
      from t
     ) t
where seqnum = 1;

Upvotes: 1

XING
XING

Reputation: 9886

In oracle you can use the below SQL query to achieve the resultset.

select name ,  
       age,    
       policy_no,  
       proposer_code,      
       entry_date ,        
       status
 from (
select name ,  
       age,    
       policy_no,  
       proposer_code,      
       entry_date ,        
       status,
       rank()over(partition by name ,age,policy_no, proposer_code order by entry_date  desc) rnk
from test
group by name ,  age,    policy_no,  proposer_code ,entry_date , status ) a
where a.rnk = 1;

Upvotes: 0

Related Questions