Nifty SQL

It's a very rare thing when neat code is also useful; in my experience "neat" or "clever" code usually also comes with some major caveats: readability and maintainability, and usually arguable usefulness being the major ones.

However, I think the self-join solution to the "greatest-n-per-group" problem might be one of the exceptions to that rule. While it certainly suffers from a lack of readability (at least personally, it requires a few mental acrobatics to comprehend), it makes up for it in its usefulness and execution speed; since it generally can work on indexes, it can avoid expensive index and/or table scans!

Really, quite a clever solution. I wonder if there's a mathematical analog to this solution?