duplicate records with different timestamp

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)

duplicate records with different timestamp

Author

Topic

thanksfor help
Posting Yak Master

106 Posts

Posted - 2008-05-21 : 18:44:57

Hi,

There duplicate records in history table with different time-stamp.
I would like to get distinct value with max(timestamp)

prodid custid sales_amt sales_qty timestamp
123 ABC 100 1 8/10/06
123 ABC 100 1 8/11/06
123 ABC 200 2 9/20/06
123 ABC 200 2 9/21/06
123 ABC 200 2 9/25/06
123 ABC 300 3 10/30/06

I need help in writing the query to get following result

prodid custid sales_amt sales_qty timestamp
123 ABC 100 1 8/11/06
123 ABC 200 2 9/25/06
123 ABC 300 3 10/30/06

Any help is very appreciated.

Thanks for your time.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-05-21 : 18:52:24

SELECT prodid, custid, sales_amt, sales_qty, MAX(timestamp) AS timestamp
FROM YourTable
GROUP BY prodid, custid, sales_amt, sales_qty

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Database maintenance routines:
http://weblogs.sqlteam.com/tarad/archive/2004/07/02/1705.aspx

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources