How do I get the nth row in a SQL Server table?

You might have occasion where you want to discover who finished second, the 10th most popular rock song, or the 5th highest paid employee. This is trival using nested TOP statements, but those can get pretty heavy if you are trying to get row 5,199,245. Here are some other options you can test (turn statistics and execution plan on, and test against large resultsets, to see the impact of the methods in different scenarios). This example assumes a unique FName, and tries to get the 10th name (in alphabetical order). 
 

CREATE TABLE Names  (      NameID INT IDENTITY(1, 1)          PRIMARY KEY CLUSTERED,      FName VARCHAR(32)  )  GO    SET NOCOUNT ON  INSERT Names(FName) VALUES('Aaron')  INSERT Names(FName) VALUES('Greg')  INSERT Names(FName) VALUES('Alex')  INSERT Names(FName) VALUES('Luan')  INSERT Names(FName) VALUES('John')  INSERT Names(FName) VALUES('Todd')  INSERT Names(FName) VALUES('Scott')  INSERT Names(FName) VALUES('Jess')  INSERT Names(FName) VALUES('Drew')  INSERT Names(FName) VALUES('Katherine')  INSERT Names(FName) VALUES('Paul')  GO    -- solution #1: nested top    SELECT TOP 1 FName  FROM  (      SELECT TOP 10 FName      FROM Names      ORDER BY FName  ) sub  ORDER BY FName DESC    -- solution #2: NOT IN    SELECT TOP 1 FName   FROM Names WHERE FName NOT IN  (      SELECT TOP 9 FName      FROM Names      ORDER BY FName  )  ORDER BY FName    -- solution #3: derived count  -- this assumes FName is unique    SELECT FName      FROM Names      WHERE       (          SELECT COUNT(*)          FROM Names n2          WHERE n2.FName <= Names.FName      ) = 10    -- solution #4: MAX    SELECT FName = MAX(FName) FROM   (      SELECT TOP 10 FName      FROM Names      ORDER BY FName  ) sub    -- solution #5: relative fetch from cursor    -- yes, cursors are generally evil, but  -- sometimes you might be surprised    DECLARE FNames CURSOR      LOCAL STATIC READ_ONLY FOR      SELECT FName          FROM Names          ORDER BY FName    DECLARE @FName VARCHAR(32)    OPEN FNames    FETCH RELATIVE 10 FROM FNames INTO @FName    CLOSE FNames  DEALLOCATE FNames    SELECT FName = @FName    DROP TABLE Names  GO

 
There are two issues that the above queries don't take into account: what if there are duplicates (ties)? and what if we want more data than the column we are ordering by? Here are some more examples. This is an employees table, and we want the employee(s) with the 7th highest salary. 
 

CREATE TABLE Employees  (      EmployeeID INT IDENTITY(1, 1)          PRIMARY KEY CLUSTERED,      LName VARCHAR(32),      Salary INT  )  GO    SET NOCOUNT ON  INSERT Employees(LName, Salary) VALUES('Bertrand', 350000)  INSERT Employees(LName, Salary) VALUES('Le' , 40000)  INSERT Employees(LName, Salary) VALUES('Smith' , 50000)  INSERT Employees(LName, Salary) VALUES('Weathers', 80000)  INSERT Employees(LName, Salary) VALUES('Martin' , 53000)  INSERT Employees(LName, Salary) VALUES('VanWyck' , 32000)  INSERT Employees(LName, Salary) VALUES('Gretzky' , 30000)  INSERT Employees(LName, Salary) VALUES('Jambon' , 30000)  INSERT Employees(LName, Salary) VALUES('DeNiro' , 50000)  INSERT Employees(LName, Salary) VALUES('Albert' , 40000)  INSERT Employees(LName, Salary) VALUES('Willis' , 70000)    -- solution #1: nested top, take aribitrary row    SELECT TOP 1 LName, Salary  FROM  (  SELECT TOP 7      LName, Salary FROM Employees      ORDER BY Salary DESC  ) sub  ORDER BY Salary    -- solution #2: nested top, order by lastname    SELECT TOP 1 LName, Salary  FROM  (  SELECT TOP 7      LName, Salary FROM Employees      ORDER BY Salary DESC, LName  ) sub  ORDER BY Salary    -- solution #3: nested top, take both rows    SELECT TOP 1 WITH TIES LName, Salary  FROM  (  SELECT TOP 7 WITH TIES      LName, Salary FROM Employees      ORDER BY Salary DESC  ) sub  ORDER BY Salary    -- solution #4: NOT IN, take both rows    SELECT TOP 1 WITH TIES LName, Salary      FROM Employees e      WHERE Salary NOT IN      (          SELECT TOP 6 WITH TIES Salary          FROM Employees          ORDER BY Salary DESC      )      ORDER BY e.Salary DESC    -- solution #5: change NOT IN to OUTER JOIN    SELECT TOP 1 WITH TIES e.LName, e.Salary      FROM Employees e      LEFT OUTER JOIN      (          SELECT TOP 6 WITH TIES Salary          FROM Employees          ORDER BY Salary DESC      ) e2      ON e.Salary = e2.salary      WHERE e2.salary IS NULL       ORDER BY e.Salary DESC    DROP TABLE Employees  GO

 
Finally, check out this generic procedure from Vyas, which uses dynamic SQL to get the nth row out of any table, based on any column. 
 
With Access, some of the above queries might work. Or, you could just repeatedly hit rs.movenext until you arrive at the requested row. Finally, you could stuff the result into a GetRows array and move to the desired position. 
 
Keep in mind that there is no 'FIRST' functionality in SQL Server. There is no concept of 'FIRST' or 'LAST' like in Access, unless you specifically say you want TOP 1 or MAX and add an ORDER BY clause. Unlike Access, which assumes there is some meaning to the physical ordering of the data on disk, SQL Server treats a set of data exactly like that - a set of unordered data. If you want it in a specific order, you have to ask for it that way.