Edamame Edamame - 3 months ago 18
SQL Question

Impala: select field with criteria when using group by

I have the following table:

id | animal | timestamp | team
---------------------------------------
1 | dog | 2016-08-01 | blue
2 | cat | 2016-08-02 | blue
3 | bird | 2016-07-05 | red
4 | cow | 2016-08-04 | red
5 | snake | 2016-08-12 | yellow


I would like to find a animal per team with the criteria that: if a team has more than one animal, we will chose the one with the later timestamp. Is this possible? Thanks!

Answer

A typical approach uses row_number():

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