Intresting subquery

Intresting query to make a sub select. For 4.0.x you'd need a temporary table instead of the subselect: CREATE TEMPORARY TABLE tmp AS SELECT x1.category, x1.itemid, COUNT(x2.category) AS rank FROM mytbl x1 LEFT JOIN mytbl x2 ON x2.category = x1.category AND x2.itemid = x1.itemid AND x2.timemodified < x1.timemodified GROUP BY x1.category, x1.itemid; SELECT r1.* FROM mytbl r1 JOIN tmp r2 ON r2.category = r1.category AND r2.itemid = r1.itemid ORDER BY r1.category, r2.rank DESC, r1.timemodified DESC;