0

I have a situation in MS SQL where two or more records in one table need to be compared, and the record with the greatest date needs to be set as the current record. I have copied the records to be compared to a temp table, and added a row number calculation. The temp table is called CANDIDACY and looks something like this:

        ID CUR_CAND RowNum Date
        1  N        1      2018-08-01
        1  N        2      2020-06-15
        1  Y        4      2021-07-22
        2  N        3      2021-04-08
        2  Y        5      2022-06-15

I would like to set CUR_CAND = Y where MAX(rn), and N for all other cases. I've tried setting CUR_CAND for all rows to N and then using an update statement to set the row with MAX(rn) to Y, but I can't figure out the syntax for the update statement. Any help is appreciated.

Dale K
  • 25,246
  • 15
  • 42
  • 71
rjohns
  • 13
  • 2
  • Something like: `SET CUR_CAND = CASE WHEN MAX(Rownum) OVER (PARTITION BY ID) = [RowNum] THEN 'Y' ELSE 'N'` I think would work. I'm assuming this is to be partitioned by `id` but you didn't state in your question. – JNevill Sep 19 '22 at 20:35
  • I actually didn't partition by anything, since this is a temp table and I wanted a distinct row number for each row. I'm using this: ROW_NUMBER() over (ORDER BY JOB_TIME) as Rownum I tried your statement and got this: Windowed functions can only appear in the SELECT or ORDER BY clauses. – rjohns Sep 19 '22 at 20:54
  • Use a [top 1 row of each group](https://stackoverflow.com/questions/6841605/get-top-1-row-of-each-group); you can then perform the `UPDATE` against the CTE. – Thom A Sep 19 '22 at 20:58
  • I was able to get it to work by doing this (cdc is my temp table) ;WITH CurrC AS (SELECT *, ROW_NUMBER() OVER(ORDER BY JOB_TIME desc) as MaxRow FROM cdc) UPDATE c SET CUR_CAND = 'Y' FROM CurrC c JOIN cdc ON c.ID = cdc.ID WHERE c.MaxRow = 1 – rjohns Sep 19 '22 at 22:36

1 Answers1

0

You may also use NOT EXISTS as the following:

UPDATE T SET CUR_CAND = 'Y'
  FROM #CANDIDACY T
WHERE NOT EXISTS (SELECT 1 FROM #CANDIDACY D WHERE D.ID=T.ID AND D.Date>T.Date)

See a demo.

ahmed
  • 9,071
  • 3
  • 9
  • 22