SQL SERVER – Getting Second Row from Table

The other day during one of my old clients sent me an email about if I know any trick to get the second row from the resultset. I know why he was asking this question as I had worked with him before Comprehensive Database Performance Health Check.

Let us see how we can retrieve the second row or the matter of the fact any row from the table. I will be using the sample database WideWorldImports for this example. Let us say the following is the query:

Get Second Row Query

SELECT OrderLineID, OrderID, StockItemID, Description
FROM WideWorldImporters.Sales.OrderLines
ORDER BY OrderID, StockItemID
GO

Here is the result which the above query produces.

Now let us say our user wants to retrieve the rows which are highlighted in the image. Let us assume that he wants to retrieve lines 2nd, 4th, and 7th. How would he go about retrieving those specified rows?

Method 1: The Older Method – Temp Table – 2nd Row

One of the most simple methods is to create a temporary table with the identity column and insert the result of this query inside that table and select the 2nd, 4th and 7th row based on the identity table.

REATE TABLE #TempTable (ID INT IDENTITY,
					OrderLineID INT, OrderID INT, StockItemID INT, Description VARCHAR(200))
GO
INSERT INTO #TempTable
SELECT OrderLineID, OrderID, StockItemID, Description
FROM WideWorldImporters.Sales.OrderLines
ORDER BY OrderID, StockItemID
GO
SELECT OrderLineID, OrderID, StockItemID, Description
FROM #TempTable
WHERE ID IN (2,4,7)
GO
DROP TABLE #TempTable
GO

Method 2: The Newer Method – Ranking Function – 2nd Row

This has been my favorite method recently. I have been using this method to retrieve any preferred row and the best part is that this method actually performs better in terms of performance. Let us see how it works.

SELECT OrderLineID, OrderID, StockItemID, Description
FROM (
SELECT ROW_NUMBER() OVER (ORDER BY OrderID, StockItemID) AS RowNum,
OrderLineID, OrderID, StockItemID, Description
FROM WideWorldImporters.Sales.OrderLines
) T
WHERE RowNum IN (2,4,7)
GO

I am very sure there are many different methods out there which we can use to find out any specific row number. I would love to know what is your preferred method and in what scenario you have to do this task.

Here are a few related blog posts on this topic:

• Sample Example of RANKING Functions – ROW_NUMBER, RANK, DENSE_RANK, NTILE
• Ranking Functions – RANK( ), DENSE_RANK( ), and ROW_NUMBER( )
• How to Get Top N Records Per Group? – Ranking Function
• SQL SERVER – Introduction to PERCENT_RANK()

Reference: Pinal Dave (https://darkslategrey-bat-805937.hostingersite.com)