How to Select max value or last record when grouping multiple columns?

Question

This is something i have been thinking for hours and still cant get the answer.

Suppose I have this table: table

+---------------------------------------------+
+     date    |  id   | Country | Impressions +
+---------------------------------------------+
+  2017-03-08 |   1   | US      |    374      +
+  2017-03-09 |   1   | US      |    521      +
+  2017-03-10 |   1   | US      |    708      +
+  2017-03-08 |   2   | US      |    100      +
+  2017-03-09 |   2   | US      |    200      +
+---------------------------------------------+

I want to group the results by id and country, but getting the impressions on the last day (max value of date or last record)

I have this query

SELECT * from table group by id,country

but it gives me this output

+---------------------------------------------+
+     date    |  id   | Country | Impressions +
+---------------------------------------------+
+  2017-03-08 |   1   | US      |    374      +
+  2017-03-08 |   2   | US      |    100      +
+---------------------------------------------+

As you can see the date is the first record.

I want a query that gives me this output

+---------------------------------------------+
+     date    |  id   | Country | Impressions +
+---------------------------------------------+
+  2017-03-10 |   1   | US      |    708      +
+  2017-03-09 |   2   | US      |    200      +
+---------------------------------------------+

As you can see the output has the maximum value on date, or last record inserted

I hope you guys can help me on this, i have been burning my head the last hours and failed to get this done.

Thank you very much in advance.

I can't because date will not always be the same, sometimes the maximum date will be different for the groups and countries — Lucas, Mar 10 '17 at 14:21

score 3 · Accepted Answer · answered Mar 10 '17 at 14:12

3

Get the max date per id,country and join this result with the original table.

SELECT t.* 
from table t
join (select id,country,max(date) as maxdate from table 
      group by id,country) t1
on t1.id=t.id and t1.country=t.country and t1.maxdate=t.date

answered Mar 10 '17 at 14:12

Vamsi Prabhala

48,685
4
36
58

score 1 · Answer 2 · answered Mar 10 '17 at 14:16

The problem is that you are thinking "group" rather than "filter". You don't want to aggregate any columns, so you really want to filter rows.

Here is one method for the filtering:

select t.*
from t
where t.date = (select max(t2.date)
                from t t2
                where t2.country = t.country and t2.id = t.id
               );

How to Select max value or last record when grouping multiple columns?

2 Answers2