Selecting the Nth row from a SQL table

Yesterday I had to find a way to get the second row in a SQL query (but the same can be applied to a table). But it should be clean and should work with the third row, fourth, and so on.

We start by having this table:

SQL table design

And this starting data:

SQL table data

And this is the SQL query that would give us the 2nd row of that table ([34, 1] with our sample data):

SELECT
TOP 1 NumberOfPosts, UserId
FROM
(
SELECT
-- Here change the "2" for the desired Nth position

TOP 2 NumberOfPosts, UserId
FROM
TopBloggers
ORDER BY
NumberOfPosts DESC
) AS NPOSTS
ORDER BY
NumberOfPosts

Comments?

Posted by Kartones on 2007-04-23