Update a row based on MaxDate

Question

Update a row based on MaxDate

pnr8uk

SSCommitted

Points: 1828
More actions
April 25, 2023 at 8:09 am

#4179082

Morning all
Does anyone know how I can update a table (one row) based on the maxdate of the row?
The problem being the id is different for the same item just used multiple times, so all the other rows for the item need to stay the same but the maxdate row needs to change to Yes
For instance the horse info needs to change to Yes (below) when the RaceDate is the MaxDate but the remaining rows need to stay as No

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 246985 More actions · Answer 1

As the Ids are different, should the grouping be based on HorseName?

You have been here long enough to provide DDL, INSERT scripts etc and have chosen not to. My suggested code is therefore untested and may not work as hoped

WITH grouped
AS (SELECT HorseName = t1.HorseName
          ,RaceDate = MAX (t1.RaceDate)
    FROM tbl t1
    GROUP BY t1.HorseName)
UPDATE t2
SET LatestInfo = 'Yes'
FROM tbl t2
    JOIN grouped
        ON grouped.HorseName = t2.HorseName
           AND t2.RaceDate = grouped.RaceDate;

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

pnr8uk SSCommitted Points: 1828 More actions · Answer 2

pnr8uk

SSCommitted

Points: 1828

April 25, 2023 at 10:35 am

#4179141

Thanks Phil

Worked a treat.

drew.allen SSC Guru Points: 76996 More actions · Answer 3

This requires reading the table TWICE. You can do it by only reading the table ONCE.

WITH HorseRacesOrdered AS
(
    SELECT t1.HorseName, t1.LatestInfo, ROW_NUMBER() OVER(PARTITION BY t1.HorseName ORDER BY t1.RaceDate DESC) AS rn
    FROM tbl t1
)
UPDATE HorseRacesOrdered
SET LatestInfo = 'Yes'
WHERE rn = 1;

Of course, this will only work if it's a one-time update. If you run this multiple times, you'll also need to update former LatestInfo to 'No'.

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA