RANK() and alternate Sub Query in T-SQL

Consider, we have data table called Orders_Header which hold order header details.

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.

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.





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.

Movex Tech Training from Lawson

My current employer (Brandix Lanka) arranged a MoveX technical training from Lawson, our ERP vendor. It took 12 days and today is the last day.

Mr. Daniel Rodrigo Olea , EMEA Technical Project Manager of Lawson software is the presenter and delivered marvelous sessions to our team.

Very first day, we were totally blank of what he is talking about. But he guided us in a way that we were able to understand almost everything in Movex code.

         

 

 

Areas that we covered in the training,

1. MAK tool
2. Creating/modifying Movex Program
3. Creating/modifying View Definition
4. Creating/modifying M3 API
5. Managing Out Interface
6. Data tables, Data Structures, CL in M3
7. Lawson web services to interact with M3 programs and APIs.
8. and much more…

View Full Album