Querying ID's with Newest Date

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

General SQL Server Forums

New to SQL Server Programming

Querying ID's with Newest Date

Author

Topic

yawnzzzz
Starting Member

13 Posts

Posted - 2010-09-02 : 09:37:24

Hi,

I have a table that looks something like this:

ItemID | Status | DateTimestamp
1 | New | '2010-09-01 8:00:00AM'
1 | Processing | '2010-09-01 8:40:00AM'
1 | Shipped | '2010-09-01 9:00:00AM'
2 | New | '2010-09-01 9:10:00AM'

I'd like to run a query that only returns one line for each order ID, and it returns that line based on the newest datetimestamp. For example:
ItemID | Status | DateTimestamp
1 | Shipped | '2010-09-01 9:00:00AM'
2 | New | '2010-09-01 9:10:00AM'

Thanks in advance for any help or direction.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-09-02 : 10:09:13

select t1.* from table as t1 inner join
(
select ItemId,max(Datetimestamp) as Datetimestamp from table
group by ItemId
) as t2
on t1.Itemid=t2.Itemid and t1.Datetimestamp=t2.Datetimestamp

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources