Top-N Per Group
Last Updated: May 3, 2026
6 min read
Getting the overall top 5 tracks by stream count is a single ORDER BY ... LIMIT 5. But getting the top 5 tracks per genre, or the 3 most recent streams per user, requires a different technique. The database needs to partition rows into groups, rank within each group, and then filter.
This pattern appears constantly in SQL interviews, and there are several ways to solve it, each with different trade-offs around ties, performance, and portability.