It’s been few times I needed to select a set of records from 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 I need all cells of a record with the biggest/smallest/whatever value of one column, and all those grouped per another column.
Finally the solution was found here and the point there is to do sorting in subquery for group select. In simple it would look like (taking example above):
SELECT * FROM (SELECT * FROM logs WHERE level=’critical’ ORDER BY created DESC) tmp_table GROUP BY user_id;
This will give me the last entry from the log with level=’critical’ per user. It is also possible to all kinds of joins and whatever in subquery. The things is that first we gather all data needed and sorted properly, and then the outed select groups it accordingly.
One additional note which has nothing to do with a query itself, but I found it out during my works related to this task. While running the above-type query on a pretty large table, I got an error from MySQL
ERROR 126 (HY000): Incorrect key file for table ‘/tmp/#sql_NNNN_x.MYI’; try to repair it
By looking around I realized that the hosting company which provided the server set up the /tmp directory of a system as a separate partition of a pretty small size and while MySQL was trying to do the requested task, it was running a bit out of space, causing a corruption of a temporary table. Specifying tmp=/var/tmp (or any other directory with some more space) in /etc/my.cnf (depending on your setup though), setting up correct permissions and restarting MySQL fixed the problem.