Update a row based on MaxDate
-
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
-
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
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin -
Thanks Phil
Worked a treat.
-
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
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply