SELECT TOP 1 * FROM (SELECT TOP n * FROM table ORDER BY column DESC) AS nthrow ORDER BY column
- This becomes horrendously inefficient for larger values of
n because it works by selecting all n rows, turning the result upside-down and then selecting the top row from that, throwing the rest away.
- This isn’t for production or large dataset, but it has its uses. I find it easier to conceptualise and quicker to write than the arcane
ROW_NUMBER() wizardry in Stack Overflow answers.
- Newer versions of SQL Server allow you to specify a
LIMIT with an
OFFSET, making this approach redundant.
- MySQL and SQLite are kind enough to provide a
LIMIT x,y function that does exactly this.