SQL Query To Get Nth Row From A Table

Today a friend of mine asked me how to select the nth row in a SQL database table. I didn't know the answer, so I googled it. Of course, there were many solutions to this problem but I picked one and tried it on my local SQL Server. I want to share it with you now. Here is the template:

SELECT * FROM

(

  SELECT ROW_NUMBER() OVER (ORDER BY col_name ASC) AS rownumber, *

  FROM table_name

) AS foo

WHERE rownumber = n

Let me give you a real example. Assume that you have a table called "items" (it is not hard to imagine a table called "items", huh?) and you want to get the row which has the 5th greatest id just because you like it that way. I won't judge you for this request, I like the 3rd greatest better... Anyway, the query you will need is going to be something like this:

SELECT * FROM

(

  SELECT ROW_NUMBER() OVER (ORDER BY id ASC) AS rownumber, *

  FROM items

) AS foo

WHERE rownumber = 5

Hope it helps...