CTE help for latest date/record

Question

Please can someone assist with writing a CTE for the following scenario?

I have the following query:

SELECT
  PEA.tblPARTY_PERSON
  ,PEA.PEAS_STATUS_DATE
  ,EAS.EAS_DESC
FROM
  tblLU_EOTAS_APPLIC_STATUS EAS
  INNER JOIN tblPARTY_PERSON_EOTAS_APPLIC_STATUS PEA
    ON EAS.EAS_ID = PEA.tblLU_EOTAS_APPLIC_STATUS
WHERE   PEA.tblPARTY_PERSON = 62649
ORDER BY PEA.PEAS_STATUS_DATE DESC

Which returns the following data:

For each Party_ID I need to return only the most recent date and corresponding data in the EAS_DESC column (I have used a WHERE clause in the SQL above to reduce the data output to just one record for convenience/testing, but will need to remove this in the final script). I'd like to use a CTE but am not sure how best to write it, please help!

Thanks in advance.

score 0 · Accepted Answer · answered Jul 12 '22 at 11:24

You can use ROW_NUMBER() to assign each row a record based on the status date, and then filter your query for only the latest record (i.e. Rownumber = 1).

WITH CTE AS
(   SELECT
        PEA.tblPARTY_PERSON
        ,PEA.PEAS_STATUS_DATE
        ,EAS.EAS_DESC
        ,ROWNUM = ROW_NUMBER() OVER(PARTITION BY PEA.tblPARTY_PERSON ORDER BY PEA.PEAS_STATUS_DATE DESC)
    FROM
        tblLU_EOTAS_APPLIC_STATUS EAS
        INNER JOIN tblPARTY_PERSON_EOTAS_APPLIC_STATUS PEA
        ON EAS.EAS_ID = PEA.tblLU_EOTAS_APPLIC_STATUS
    --WHERE   PEA.tblPARTY_PERSON = 62649
)
SELECT  CTE.tblPARTY_PERSON, CTE.PEAS_STATUS_DATE, CTE.EAS_DESC
FROM    CTE
WHERE   CTE.ROWNUM = 1
ORDER BY CTE.PEAS_STATUS_DATE DESC;

N.B. I've used a CTE as you said you'd like to use one, but this is just syntactic sugar, and the actual solution has nothing to do with a CTE and can be written just as easily using a subquery:

SELECT  t.tblPARTY_PERSON, t.PEAS_STATUS_DATE, t.EAS_DESC
FROM    (   SELECT
                PEA.tblPARTY_PERSON
                ,PEA.PEAS_STATUS_DATE
                ,EAS.EAS_DESC
                ,ROWNUM = ROW_NUMBER() OVER(PARTITION BY PEA.tblPARTY_PERSON ORDER BY PEA.PEAS_STATUS_DATE DESC)
            FROM
                tblLU_EOTAS_APPLIC_STATUS EAS
                INNER JOIN tblPARTY_PERSON_EOTAS_APPLIC_STATUS PEA
                ON EAS.EAS_ID = PEA.tblLU_EOTAS_APPLIC_STATUS
            --WHERE   PEA.tblPARTY_PERSON = 62649
        ) AS t
WHERE   t.ROWNUM = 1
ORDER BY t.PEAS_STATUS_DATE DESC;

Both solutions are absolutely spot on, thank you very much! I'm trying to get a handle on using CTEs, which is why I asked for one in this instance, and someone advised using one in a similar situation in the past. I was also going to experiment with the sub-query option, so having that outlined as well is a real help. Thank you again — JamesL24, Jul 13 '22 at 07:14

CTE help for latest date/record

1 Answers1