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.

SQL SERVER - Getting Second Row from Table 2nd-row-800x498

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.

Solarwinds

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?

SQL SERVER - Getting Second Row from Table secondrow1

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

SQL SERVER - Getting Second Row from Table secondrow2

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

SQL SERVER - Getting Second Row from Table secondrow2

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:

Reference: Pinal Dave (https://blog.sqlauthority.com)

Solarwinds
, , , ,
Previous Post
SQL SERVER – Group By Orders Data by Columns Ascending
Next Post
SQL SERVER – Creating a Nonclustered Columnstore Index on Temp Table

Related Posts

8 Comments. Leave new

  • Jonathan Roberts
    October 28, 2019 7:11 am

    You could get better performance from the first query if you selected TOP(7) like this:
    REATE TABLE #TempTable (ID INT IDENTITY,
    OrderLineID INT, OrderID INT, StockItemID INT, Description VARCHAR(200))
    GO
    INSERT INTO #TempTable
    SELECT TOP(7) 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

    Reply
  • Why not use something like OFFSET and Fetch:
    CREATE TABLE #tbl (someNumber INT)

    INSERT INTO #tbl VALUES (1)
    INSERT INTO #tbl VALUES (2)
    INSERT INTO #tbl VALUES (3)

    SELECT someNumber FROM #tbl ORDER BY someNumber
    OFFSET 1 ROWS FETCH NEXT 1 ROWS ONLY;

    DROP TABLE #tbl

    Reply
  • Ahmad El Harake
    October 29, 2019 12:51 am

    SELECT * FROM Customers WHERE ROWNUM = 3;
    This is to retrieve 3rd row in SQL.

    Reply
  • Where did you use RowNum as the alias? I see T..

    Also doesn’t SQL have a function RANK() ?

    Reply
    • Rank will not work as we need only rows. Rank will give a very different results if there is tie in the columns used in the where ORDER BY clause.

      Reply
  • Shantilal Suthar
    October 31, 2019 7:21 pm

    In short, “how to retrieve every second row from a table”

    We can achieve this by the following query too:

    SELECT
    FROM (
    SELECT ROW_NUMBER() OVER (ORDER BY OrderID, StockItemID) AS RowNum,
    OrderLineID, OrderID, StockItemID, Description
    FROM WideWorldImporters.Sales.OrderLines
    ) T
    WHERE RowNum % 2 = 0
    GO

    In the above query, we perform the remainder on RowNum by 2.

    Hope this helps.

    Thanks.

    Reply
  • Your example has #temp table and sub query but with rank functions CTE is works good rite ?

    Reply

Leave a Reply

Menu