Search code examples
sqlgroup-byimpala

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!

Solution

  • 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;