Select only nth row in T-SQL (pre-2012)
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.

similar posts