Retrieve last updated record for each row

Question

Post reply

Retrieve last updated record for each row

bhavnabakshi

SSC Eights!

Points: 848
More actions
October 17, 2008 at 9:09 am

#214140

I have created a table which tracks any changes in table A. At the end of the month, I have to retrieve all the records that have been updated in last one month. There is a possibility one record is updated multiple times. I tried to use max on each column I'm retrieving, but I get max on the column instead last updated record for each row.
Here is the code:
SELECT MAX(CHANGEDATETIME), MAX(ID) as ID, MAX(CO_ID) AS CO_ID, MAX(NewMEMBERTYPE) AS Member_type,
MAX(COMPANY) as COMPANY, MAX(ADDRESS_1) AS ADDRESS_1, MAX(ADDRESS_2) AS ADDRESS_2, MAX(STATE_PROVINCE) AS STATE_PROVINCE,
MAX(CITY) AS CITY, MAX(ZIP) AS ZIP, MAX(COUNTRY) AS COUNTRY, MAX(COUNTY) AS COUNTY, MAX(COLLEGEID) AS COLLEGEID,
MAX(MEM_TYPE) AS MEM_TYPE, MAX(CB_CONTROL) AS CB_CONTROL, MAX(IPEDS) AS IPEDS, MAX(NewStatus) AS STATUS, MAX(NewJoinDate) AS JOIN_DATE
FROM MemberTypeChanges
GROUP BY ID
Thanks!

Viewing 6 posts - 1 through 5 (of 5 total)

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

Tommy Bollhofer SSChampion Points: 14940 More actions · Answer 1

Not sure if I understand exactly what you are trying to accompish but try using a CTE - i.e.

WITH LastUpdated AS

(SELECT ROW_NUMBER() OVER (ORDER BY CHANGEDATETIME DESC) AS RowID, * FROM MemberTypeChanges )

SELECT TOP 100 * FROM LastUpdated

Tommy

Follow @sqlscribe

Steve Jones - SSC Editor SSC Guru Points: 736760 More actions · Answer 2

Do you want to pull those records with multiple updates as all the updates or just the last one?

bhavnabakshi SSC Eights! Points: 848 More actions · Answer 3

I have to pull new changes in last one month. If there is a duplicate pull the last one. For e.g.

Join_Date ID Mem_Type Status

1. 10/16/2008 4:13:41 PM000008151M I

2. 10/17/2008 10:42:50 AM000008151MSA D

3 NULL 200356887NM A

record # 1 and #2 has been updated twice, but I want to retrieve only the recent update. In the extract I want to show record # 2 and 3. Whereas, I am getting "I" instead of "D" value in status column. Hope this make sense.

Thanks

noeld SSC Guru Points: 96590 More actions · Answer 4

bhavnabakshi (10/17/2008)
I have to pull new changes in last one month. If there is a duplicate pull the last one. For e.g.
Join_Date ID Mem_Type Status
1. 10/16/2008 4:13:41 PM000008151M I
2. 10/17/2008 10:42:50 AM000008151MSA D
3 NULL 200356887NM A
record # 1 and #2 has been updated twice, but I want to retrieve only the recent update. In the extract I want to show record # 2 and 3. Whereas, I am getting "I" instead of "D" value in status column. Hope this make sense.
Thanks

How about

SELECT t.Join_Date,t.ID,t.Mem_Type, t.Status

FROM

( SELECT ROW_NUMBER() OVER (PARTITION BY ID ORDER BY Join_Date DESC) AS n, * FROM MemberTypeChanges

) t

WHERE t.n = 1

* Noel

bhavnabakshi SSC Eights! Points: 848 More actions · Answer 5

Thanks Noel. I'm getting the correct results now.

Thank you!