Emulate sql Server ROW_COUNT() for SQL Server Version 8.00.760

0
votes

I need to make a query with the ROW_NUMBER() function in order to enumerate all purchase order Lines order by the PurchaseID and date of delivery:

SELECT
    PL.PURCHID,
    PL.LINENUMBER,
    ROW_NUMBER() OVER (PARTITION BY PL.PURCHID
                       ORDER BY PL.LineNumber, PL.DELIVERYDATE DESC) AS LineNumberOrdered
FROM AXDB13_R1.dbo.PURCHLINE PL
WHERE PL.DATAAREAID = 'pol';

the thing is that the database production is too old that does not recognize that function. How can I emulate that?

thanks a lot for your help!

1
sql-serverrow-number
Please include the current query. - Tim Biegeleisen
Select PL.PURCHID, PL.LINENUMBER, ROW_NUMBER() OVER (PARTITION BY PL.PURCHID ORDER BY PL.LineNumber, PL.DELIVERYDATE DESC) as LineNumberOrdered from AXDB13_R1.dbo.PURCHLINE PL Where PL.DATAAREAID = 'pol' - Nestor C.
I have attempted an answer below, please try it. - Tim Biegeleisen
Post sample data the demonstrates your issue in a consumable format (i.e., not jammed into a comment). It would also help to know what the primary and unique keys are for this table. - SMor

1 Answers

0
votes

We can replace your call to ROW_NUMBER with a correlated count query, which generates counts over what previously was each partition of PURCHID:

SELECT
    PL.PURCHID,
    PL.LINENUMBER,
    (SELECT COUNT(*)
     FROM AXDB13_R1.dbo.PURCHLINE PL2
     WHERE PL2.PURCHID = PL.PURCHID AND
           (PL2.LineNumber < PL.LineNumber OR
            PL2.LineNumber = PL.LineNumber AND
            PL2.DELIVERYDATE >= PL.DELIVERYDATE)) AS LineNumberOrdered
FROM AXDB13_R1.dbo.PURCHLINE PL
WHERE
    PL.DATAAREAID = 'pol';