CODEWITHSUNDEEP
mysqlsqlaggregate-functions
Zerium
Zerium

Reputation: 17333

MySQL: Select last row in each aggregate group

I want to select the last row of each GROUP BY aggregate in MySQL.

For example, if I write GROUP BY foo, and two rows have a foo = 'bar', I want to select the last row (as ordered by id) of those two (I want to select the message column of the last row in each aggregate clause, as ordered by id and GROUPed by foo).

How can I do this?

Table definition, as requested:

+-----------------+
| id  foo message |
+-----------------+
| 1   bar hey     |
| 2   joe hi      |
| 3   bar hello   |
+-----------------+

I want to get something like the following:

+-------------+
| foo message |
+-------------+
| bar hello   |
| joe hi      |
+-------------+

It gives me hello, because hello is the last row with foo = 'bar', as ordered by id.

Upvotes: 3

Views: 2877

Answers (2)

Blank
Blank

Reputation: 12378

Use IN and subquery:

select *
from yourtable
where (id, foo) in (
    select max(id), foo from yourtable group by foo
)

SQLFiddle Demo

Upvotes: 2

1000111
1000111

Reputation: 13519

Here's the query:

SELECT 
footable.foo,
footable.message
FROM footable
INNER JOIN 
(
    SELECT 
    foo,
    MAX(id) max_id
    FROM footable
    GROUP BY foo
) AS t
ON footable.foo = t.foo AND footable.id = t.max_id
ORDER BY footable.id DESC;

SQL FIDDLE DEMO

Running the query on your given input you will get an output like below:

| foo | message |
|-----|---------|
| bar |   hello |
| joe |      hi |

Upvotes: 4

Related Questions