CODEWITHSUNDEEP
sqlgroup-byimpala
Edamame
Edamame

Reputation: 25366

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!

Upvotes: 0

Views: 75

Answers (2)

Zoltan
Zoltan

Reputation: 3105

You could use the following query for example:

select * from teams t1 where `timestamp`=(select min(t2.`timestamp`) from teams t2 where t2.team = t1.team);

In practice:

[localhost:21000] > create table teams(id int, animal string, `timestamp` timestamp, team string);
[localhost:21000] > insert into teams values (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");
[localhost:21000] > select * from teams t1 where `timestamp`=(select min(t2.`timestamp`) from teams t2 where t2.team = t1.team);
+----+--------+---------------------+--------+
| id | animal | timestamp           | team   |
+----+--------+---------------------+--------+
| 1  | dog    | 2016-08-01 00:00:00 | blue   |
| 3  | bird   | 2016-07-05 00:00:00 | red    |
| 5  | snake  | 2016-08-12 00:00:00 | yellow |
+----+--------+---------------------+--------+

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269723

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;

Upvotes: 1

Related Questions