How to select a row based on its row number?

Question

I'm working on a small project in which I'll need to select a record from a temporary table based on the actual row number of the record.

How can I select a record based on its row number?

Answer 1

A couple of the other answers touched on the problem, but this might explain. There really isn't an order implied in SQL (set theory). So to refer to the "fifth row" requires you to introduce the concept

Select *
From 
(
    Select 
      Row_Number() Over (Order By SomeField) As RowNum
    , *
    From TheTable
) t2
Where RowNum = 5

In the subquery, a row number is "created" by defining the order you expect. Now the outer query is able to pull the fifth entry out of that ordered set.

Answer 2

Technically SQL Rows do not have "RowNumbers" in their tables. Some implementations (Oracle, I think) provide one of their own, but that's not standard and SQL Server/T-SQL does not. You can add one to the table (sort of) with an IDENTITY column.

Or you can add one (for real) in a query with the ROW_NUMBER() function, but unless you specify your own unique ORDER for the rows, the ROW_NUMBERS will be assigned non-deterministically.