How to select the Nth row in a SQL database table

Pulled from StackOverflow.com: http://stackoverflow.com/questions/16568/how-to-select-the-nth-row-in-a-sql-database-table

Also references: http://troels.arvin.dk/db/rdbms/#select-limit

SELECT * FROM (
  SELECT
    ROW_NUMBER() OVER (ORDER BY key ASC) AS rownumber,
    columns
  FROM tablename
) AS foo
WHERE rownumber <= n

Use the following example to restart the numbering when a field changes, use the 'partition by' clause.

select a.*
   , LineID = row_number() over(partition by a.Test1 order by a.RowID asc)
from Table1 a
order by a.Test1

In this example, the value of LineID will be reset to 1 whenever the value of the column [Test1] changes.