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://blog.sqlauthority.com)
10 Comments. Leave new
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
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
Totally!
SELECT * FROM Customers WHERE ROWNUM = 3;
This is to retrieve 3rd row in SQL.
Where did you use RowNum as the alias? I see T..
Also doesn’t SQL have a function RANK() ?
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.
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.
Your example has #temp table and sub query but with rank functions CTE is works good rite ?
?
I am looking for 2 rows out of multi rows, from all groups of rows. I do not know in each group what row number I need. To me that is ludicrous. I want first row ID# = SPID# and TR# = 202220 and (I just need to know if the next record exist) ID# = SPID# and TR# = 202227. If these two rows are in the same table then I want a a row on the report.