Wiki

This page is read only. You can view the source, but not change it. Ask your administrator if you think this is wrong.

====== SQL - GROUP BY - GROUP BY with max in subgroups ======

To select a set of records from a SQL table grouped by one column, but sorted by another within a group.  For example selecting a list of log entries with the last entry per user or something like that.

It is pretty simple when one needs a maximum value of a particular column per key, but what if you need all cells of a record with the biggest/smallest/whatever value of one column, and all those grouped per another column.

The solution is to do sorting in the subquery for the group select.

<code sql>
SELECT * FROM (SELECT * FROM logs WHERE level='critical' ORDER BY created DESC) tmp_table GROUP BY user_id;
</code>

This will give the last entry from the log with level='critical' per user.  

It is also possible for all kinds of joins and whatever in the subquery.  The things is that first gather all data needed and sorted properly, and then the outer select groups it accordingly.