How To Select nth Row From A Table In SQL Server

In this article I will show you how can we select nth row based on our requirement from a SQL Server table. Steps needed for this is first of all, select top n rows first from that table, then select the topmost row which is the nth row in those selected n rows.  I will be using SQL Server 2008 R2 and AdventureWorks database to describe things.

Have a look at the following image, its the data from HumanResources.Employee table present in AdventureWorks database.

In upper image, the data is random i.e its not arranged according to ContactID neither in ascending nor descending order. Now selecting nth row from this kind of data is a little bit tricky, but selecting nth row from the data which is sorted in some manner is a bit easy. As an example, have a look at the following image, in which data is sorted according to ContactID in increasing order. So, selecting nth record from the data which is sorted in some manner as in the following image is easy.

Instructions:

1. Selecting nth record by arranging rows in some order
   If data is arranged in some order, then selecting nth row is quite a bit easy. All we have to do is select the topmost n rows from the arranged data (if arranged in ascending order ) and then select the the topmost row by altering the order of the fetched data. Let me clear out the things. Say you have number 1,3,5,7,9 and you want to select 2nd row i.e n is 2, then select top 2 (n) rows which  will give you 1,3 as result. Now arrange this result in altered order i.e descending order which will give you 3,1 and now you can select the topmost row to select nth record i.e 3. If initially the data is arranged in descending order i.e 9,7,5,3,1 and we want to select 2nd row, then select top two rows which will give us 9 and 7, then alter the order from descending to ascending which will give us 7, 9 and now we can select the topmost row which will give us our result the nth record i.e 7. Here is how can we do the same in SQL Server. 
   We will be using nested queries to filter out our result. The innermost query is the first step i.e selecting the top n rows from the arranged data. Here is the result for n = 21
   
   Now from this filtered 21 rows, we will arrange the data in descending order which will give us the following result
   
   Now from above result select the topmost row and we will get our desired result

   SELECT TOP 1 *
FROM (
        SELECT TOP 21
            [ContactID]
        FROM HumanResources.Employee
        ORDER BY [ContactID] ) temp
    ORDER BY temp.[ContactID] DESC

   

2. Selecting nth record without arranging rows in any order
   Now our data is not arranged in any order and we want to select 21st record from the same table. For this, we will select the 21 rows first, after that we can’t just reverse the order of selected data to get our desired result because in this case our initial data is random. So, for this case, we will use a different method which can be used for arranged data as well. For this case we will select the top n rows first of all same as above case. We will also select top n-1 rows from the same data. and then we can subtract the result obtained by selecting n-1 rows from the result obtained by selecting n rows which will give us a single result which is the nth row in original data.
   First of all start by selecting topmost 21 rows i.e n rows
   
   Now the same way we can select 20 ( n- 1 ) rows, and using the following query we can subtract the two results to get the desired result.

   SELECT [ContactID]
FROM (
        SELECT TOP 21 [ContactID]
        FROM [AdventureWorks].[HumanResources].[Employee] ) temp
WHERE
    [temp].[ContactID] NOT IN ( SELECT TOP 20 [ContactID]
                                FROM [AdventureWorks].[HumanResources].[Employee] )

   

• how to find the row number in sql server usinsg asp net (1)
• how to select nth row in sql using C# (1)
• Steps required to select the 2nd row of the table (1)