Came across a bit of a strange problem with an ad-hoc query. Basically I have a log of categorisation actions against a realm dataset for which I needed to find a list of realms in the last month where the last action on a pkey group (realm + userid, realms can multiple userid’s) different from the first action, and the first action was ‘A’. Luckily there’s an indexed ‘logdate’ field, otherwise I would have no idea how to find this data in a single query – so I’m extremely keen for feedback from anyone who could do it with elegance! Ne’er a dirtier subselect has ye seen :D

SELECT
    `realm`, `logdate`, `pl`.`action`, `cat`,  `u`.`username`, `c`.`title` AS catname,
    @rlm:=`realm`, @own:=`pl`.`userid`, @parentcat:=`cat`,
    (CASE `logdate` WHEN MAX(`logdate`) THEN CONCAT(`action`, '_', `cat`) ELSE
        (SELECT
            CONCAT(`action`, '_', `cat`)
        FROM
            process_log AS pl
        WHERE
            `realm` = @rlm
        AND
            `userid` = @own
        AND
            `cat` != @parentcat
        ORDER BY
            `logdate` DESC
        LIMIT 1)
    END) AS recategorised
FROM
    process_log AS pl
JOIN
    users AS u
ON
    (u.userid = pl.userid)
LEFT JOIN
    categories AS c
ON
    (c.catid = pl.cat)
WHERE
    realm != ''
AND
    (`logdate` BETWEEN DATE_SUB(NOW(), INTERVAL 1 MONTH) AND NOW())
GROUP BY
    `realm`, `pl`.`userid`
HAVING
    (`pl`.`action` = 'A' AND recategorised IS NOT NULL)
ORDER BY
    `realm`, `logdate` DESC