This is totally and 100% cheating. If someone catches you doing this, the SQL police will come after you and cite you for not using a self-join. Just so you know.

Let’s say you have a table that keeps track of users, colors, and timestamps, and you want to know the most recent color for each user. It might look something like:

Table: user_colors

user color timestamp
Sophie green 2019-08-12 12:34:56
Sophie blue 2019-03-28 10:42:23
Gwen red 2018-12-12 03:23:12
Sophie purple 2013-09-12 04:52:23
Gwen fuchsia 2017-07-02 11:12:13

There are two standard solutions.

SELECT `user`, `color`
FROM (
	SELECT `user`, MAX(`timestamp`) AS `timestamp`
	FROM `user_colors`
	GROUP BY `user`
) `alias1`
INNER JOIN `user_colors`
USING (`user`, `timestamp`)

Or you could do a left join and handle this with the criterion for joining.

SELECT `user`, `color`
FROM `user_colors` AS `uc1`
LEFT JOIN `user_colors` AS `uc2`
ON (`uc1`.`user` = `uc2`.`user` AND `uc1`.`timestamp` < `uc2`.`timestamp`)
WHERE `uc2`.`user` IS NULL

I can never remember how to do the second one, and full disclosure, it might be wrong. I don’t have anything easy around to test it on. I frequently get the direction wrong on the inequality. Sometimes I forget the WHERE clause until I look at my results and notice that they are totally not what I wanted.

Don’t read any further if you are worried about the SQL police.

For whatever reason, there is something about my tables and their size and their structure that makes these self-joins really slow. I have come up with a work-around.

Behold:

SELECT `user`, SUBSTRING_INDEX( GROUP_CONCAT( `color` 
	ORDER BY `timestamp` DESC
	SEPARATOR "," ), ",", 1 )
FROM `user_colors`
GROUP BY `user`

The GROUP_CONCAT makes a comma-separted list of all the colors for the user, in descending order by timestamp. The SUBSTRING_INDEX takes the part of the string before the first comma. To be honest, I have no idea what sort of witchcraft the query planner is doing to make this work, but on my tables it is faster than the self-join.

Note that a GROUP_CONCAT will fail silently and horrifically if you have more than a thousand characters in a group. There are ways around this, but the finer points tend to depend on the system that you’re using.