RANK() and alternate Sub Query in T-SQL

Consider, we have data table called Orders_Header which hold order header details.
Table
As you can see, PO number(PONo)  ‘0050426995’ is duplicating. Because this table containing PO amendments too. If you notice, DownloadData is in order, meaning first version of PO received on 2010-06-26 17:07:04.910, rest of them subsequently.
The requirement is we want to get following output.
output
Normally we can write a sub query for this and get the output.
SELECT PONo,
(
SELECT COUNT(PONo)+1 -- to omit zero
FROM ORDERS_Header WHERE PONo=oh.PONo AND DownloadDate< oh.DownloadDate
) AS Revision,
DownloadDate
FROM ORDERS_Header oh
ORDER BY PONO,DownloadDate

By running this query, you can see the following Actual Execution Plan.

Sub_plan



From MS-SQL 2005 onwards this can be easily achieved by RANK() function. 


SElECT PONo, 
RANK() OVER(PARTITION BY PONo ORDER BY DownloadDate) AS Revision ,DownloadDate 
FROM dbo.ORDERS_Header
ORDER BY PONO,DownloadDate



Check its Actual Execution Plan and note the difference.

Rank_plan

Comments

Popular posts from this blog

IIS 7.5: 401 Unauthorized Access Error (Keep prompting Username/password)

Calculating Elapsed Time Accurately (C#)

Connecting DB2/iSeries From .net Application