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.

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

| improve this answer | |
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.

| improve this answer | |

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