think123 think123 - 4 months ago 15
SQL Question

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
GROUP
ed 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
.

Answer

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 |
Comments