1

The table is laid out like

   name, id, attr1, attr2, data_date
    a,    1,  sals, ksdjf, 05-25-2018
    b,    1,  laskd, lskd, 05-20-2017
    c,    2,  klss,  kjfe, 03-20-2018
    d,    2,  aow , lww ,  02-10-2018
 .....
select name, id, attr1, attr2, data_date from table1
where (id,data_date) in 
(select id, max(data_date) from table1 group by id)

The above query works. Essentially for each ID, I want to select the row with the max( data_date ). I am sure there is a better way with analytic functions.

Results of my query should be 
 
name, id, attr1, attr2, data_date
a, 1, sals, ksdjf,05-25-2018
c, 2, klss, kjfe, 03-20-2018

Appreciate any help to rewrite it better. Thank you.

0

2 Answers 2

1

I believe this is what you are looking for, using a window function:

select name, 
    id, 
    attr1, 
    attr2, 
    max(data_date) over (partition by id) as maxdata_date 
from table1

Results:

name    id  attr1   attr2   maxdata_date
a       1   sals    ksdjf   2018-05-25
b       1   laskd   lskd    2018-05-25
c       2   klss    kjfe    2018-03-20
d       2   aow     lww     2018-03-20

http://sqlfiddle.com/#!18/109b5/1

1

You can enumerate the rows per id ordered by data_date using ROW_NUMBER():

select name, id, attr1, attr2
           , row_number() over (partition by id 
                                order by data_date desc) as rn 
from table1

and then pick the first one in each partition:

select name, id, attr1, attr2, data_date
from (
    select name, id, attr1, attr2
         , row_number() over (partition by id 
                              order by data_date desc) as rn 
    from table1
) as t 
where rn = 1;

Note that the order by is desc since you want the last one. A common mistake is to order in ascending order and pick the last one in each partition, but that is inefficient.

Not the answer you're looking for? Browse other questions tagged or ask your own question.