how to use distinct on multiple columns

Question

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.

What you want is interesting to know. What have you tried? You tagged it with analytic-functions, so at least you know what tool to use (or was the automatic tag chooser smart enough to pick it for you?) — , Aug 06 '16 at 11:55
Am waiting for someone who can now use Dense_rank and produce the same result. Coz i already provide using rank and someone repeated the same using row_number:-)))) — XING, Aug 08 '16 at 08:40

score 1 · Answer 1 · answered Aug 07 '16 at 15:55

1

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;

answered Aug 07 '16 at 15:55

Gordon Linoff

1,242,037
58
646
786

score 0 · Answer 2 · answered Aug 07 '16 at 15:18

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;

how to use distinct on multiple columns

2 Answers2