How to update records based on the most recent dat

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

How to update records based on the most recent dat

Author

Topic

pras2007
Posting Yak Master

216 Posts

Posted - 2008-05-20 : 13:52:45

Hello All,

I have a table below that consist on “Report_Date” and “ID”. The question is that, how would I update the table to give me the most recent Report_date data? Example is shown below:

Before:
Report_Date ID
2/1/2006 11122
6/2/2007 11122
2/1/2006 11122
5/23/2007 5648
6/4/2005 9318
5/23/2007 8888

After:
Report_Date ID
6/2/2007 11122
5/23/2007 5648
6/4/2005 9318
5/23/2007 8888

Please advice. Thanks.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2008-05-20 : 13:55:03

SELECT MAX(report_date), ID
FROM MyTable
GROUP BY ID

ssunny
Posting Yak Master

133 Posts

Posted - 2008-05-20 : 13:58:54

select * from mytable order by Report_Date desc

pras2007
Posting Yak Master

216 Posts

Posted - 2008-05-20 : 15:55:09

Thanks guys for your response...

Lamprey, your query works, but do you know how to update the existing table? Does anyone know? Please advice. Thanks.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2008-05-20 : 17:59:59

Something like this should work if I understand what you are trying to do:

DELETE
	T
FROM
	MyTable AS T
LEFT OUTER JOIN
	(
		SELECT MAX(report_date), ID
		FROM MyTable
		GROUP BY ID
	) AS Temp
	ON T.ID = Temp.ID
	AND T.Report_Date = Temp.Report_date
WHERE
	Temp.ID IS NULL

pras2007
Posting Yak Master

216 Posts

Posted - 2008-05-27 : 09:58:52

Lamprey, thanks for the response. When I run your delete statement, I get the following error message: "No column was specified for column 1 of 'Temp'." Does anyone know how to resolve? Please advice. Thanks.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-05-27 : 10:02:36

quote:
Originally posted by Lamprey

Something like this should work if I understand what you are trying to do:
DELETE
T
FROM
MyTable AS T
LEFT OUTER JOIN
(
SELECT MAX(report_date) as report_date, ID
FROM MyTable
GROUP BY ID
) AS Temp
ON T.ID = Temp.ID
AND T.Report_Date = Temp.Report_date
WHERE
Temp.ID IS NULL

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-05-27 : 10:04:18

DELETE		t
FROM		MyTable AS t
INNER JOIN	(
			SELECT		ID,
					MAX(report_date) AS report_date
			FROM		MyTable
			GROUP BY	ID
		) AS w ON w.ID = t.ID
WHERE		t.Report_Date < w.Report_date

E 12°55'05.25"
N 56°04'39.16"

pras2007
Posting Yak Master

216 Posts

Posted - 2008-05-27 : 13:53:06

Thanks Peso for your response, it works! Now I want to implement this to the real table. The real table has the following columns:

Report_Date
ID
First_Name
Last_Name
Price
Quantity

Does anyone know how to incorporate the remaining column names into the delete query? Thanks.

pras2007
Posting Yak Master

216 Posts

Posted - 2008-05-27 : 14:08:46

I wrote the code this way and it is not deleting any records:

DELETE
T
FROM
MyTable AS T
LEFT OUTER JOIN
(
SELECT MAX(RPT_DATE_DETAIL)AS RPT_DATE_DETAIL, ID, First_Name, Last_Name, Price, Quantity
FROM MyTable
GROUP BY ID, First_Name, Last_Name, Price, Quantity
) AS Temp
ON T.ID = Temp.ID
AND T.RPT_DATE_DETAIL = Temp.RPT_DATE_DETAIL
AND T.First_Name = Temp.First_Name
AND T.Last_Name = Temp.Last_Name
AND T.Price = Temp.Price
AND T.Quantity = Temp.Quantity
WHERE
Temp.ID IS NULL

Please advice.

Thanks.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-05-27 : 16:11:39

Them there are no duplicates.
Are you sure you need all those columns in the duplicate check?

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources