The most recent one I can think of was to renumber an ordered collection when an interval space gets full. There was a blog post but I can't find it now. Here's an executed SQL query. The source would have variables in place of most of the numeric values. I think this one finds now many rows need to be renumbered (shuffle_rank) and what the cut-off position is at that point. There's a corresponding UPDATE that does the renumber of the shuffle_rank count of rows that evenly respaces the position values between the available number space previously occupied by the rows being renumbered.
SELECT shuffle_rank, position
FROM (SELECT @shuffle_rank := @shuffle_rank + 1 AS shuffle_rank, position
FROM `gallery_items`
JOIN (SELECT @shuffle_rank := 0) initialize_rank_var
WHERE `gallery_items`.`gallery_id` = 14103882 AND (position >= 120533287)
ORDER BY position ASC) positionable_items
WHERE ABS(120533287 - position) >= shuffle_rank + 0 LIMIT 1
They were a valuable pair of queries because it was killing the master mysql db preventing it from scaling further beyond the 10K's (of 1M+) online users.
I had also written a mainframe DB2 query that spanned 2 and 1/2 letter sized pages that took several minutes to run. It made extensive use of CTEs and correlated subqueries with guard hueristics (to bound time intervals) to limit the work of exact subqueries to find something (that I can't recall now) in student loan histories. It required a couple updates/patches to DB2 to just let the should-be valid SQL to run. I probably spent about two weeks just to come up with the one query and optimize it down to that duration.