[Drew Rosen]

I have the following table:

Prikey         orderprikey    statusdate
1                       5                    01/02/17
2                       5                    02/05/17
3                       100               03/01/17
4                       100                04/05/17
5                        6                    01/02/17

I would like to select the  with 1 record per orderprikey with the most current date ie

2                          5                  02/05/17
4                        100                 04/05/17
5                           6                  01/02/17

I tried the following

Select distinct prikey,orderprikey,MAX(statusdate) as statusdt
from Table
goup by prikey,orderprikey,statusdate

thanks for your help

——————————
Drew Rosen
Carolina Services of Fayetteville, Inc
Fayetteville NC
——————————

[Matthew Arp]

Assuming that prikey is an Identity column (strictly increases) then the below will work.

SELECT t1.* FROM Table t1 INNER JOIN (SELECT MAX(prikey) prikey GROUP BY orderprikey) t2 ON t2.prikey=t1.prikey

——————————
Matthew Arp
Business Systems Developer
Hunton Group
Houston TX
——————————
——————————————-

[Valerie Sikora]

I think your script will work if you remove the statusdate from the group by statement.

Thanks
Val

——————————
Valerie Sikora
InterDyn BMI
Roseville MN
——————————
——————————————-

[Jeff Pfershy]

You can also use a CTE (Common Table Expression) to apply a row number to the orderprikey column and sort based on the statusdate in descending order. This creates a phantom row number for each statusdate record within the same orderprikey, and every time the orderprikey changes, the numbering starts again at 1.

WITH My_CTE AS
(
 SELECT
    orderprikey
      ,statusdate
      ,ROW_NUMBER() OVER(PARTITION BY orderprikey ORDER BY statusdate DESC) AS ‘RowNumber’  
    FROM table1
)

SELECT
   orderprikey
  ,statusdate
FROM My_CTE
WHERE RowNumber = 1
ORDER BY 1

If I wanted to apply the same logic to AR invoices in GP and say, just get the most recent invoice for each GP customer, I could do something like this:

WITH Sales_CTE AS (
 SELECT
    CUSTNMBR as ‘CustomerID’
      ,DOCNUMBR as ‘InvoiceNumber’
      ,DOCDATE as ‘InvoiceDate’
      ,ORTRXAMT AS ‘InvoiceAmt’
      ,ROW_NUMBER() OVER(PARTITION BY CUSTNMBR ORDER BY DOCDATE DESC,ORTRXAMT DESC) AS ‘RowNumber’  
—      ,ROW_NUMBER() OVER(PARTITION BY CUSTNMBR, DOCDATE ORDER BY DOCDATE DESC,ORTRXAMT DESC) AS ‘RowNumber’  
    FROM RM20101
      WHERE RMDTYPAL = 1
        AND VOIDSTTS = 0
        AND ORTRXAMT > 0)

SELECT
    CustomerID
   ,InvoiceNumber
   ,InvoiceDate
   ,InvoiceAmt
FROM Sales_CTE
WHERE RowNumber = 1
ORDER BY 1

Hope that helps

——————————
Jeff Pfershy
Solution Consultant
248-489-0707
The TM Group, Inc.
Farmington Hills MI
——————————
——————————————-