January 9, 2008 at 12:25 pm
I have the following records in a table:
ItemID StatusDate Status Description
-------------------------------------------
501 1/8/2008 open identified
501 1/9/2008 closed
502 1/9/2008 open identified
503 1/8/2008 open identified
503 1/9/2008 closed
I want to pick up the highest statusdate from the itemID group i.e.
501 1/9/2008 closed
502 1/9/2008 open identified
503 1/9/2008 closed
How do I create a sql query to get the needed result?
Any help will be appreciated!
🙂
January 9, 2008 at 12:38 pm
Try this:
create table #TestTable (
ItemID int,
StatusDate datetime,
Status varchar(6),
Description varchar(25)
)
insert into #TestTable (
ItemID,StatusDate,Status,Description
)
select 501,'2008/01/08','open','identified' union all
select 501,'2008/01/09','closed','' union all
select 502,'2008/01/09','open','identified' union all
select 503,'2008/01/08','open','identified' union all
select 503,'2008/01/09','closed',''
select
t1.*
from
#TestTable t1
inner join (select
t2.ItemID as ItemID,
max(t2.StatusDate) as StatusDate
from
#TestTable t2
group by
t2.ItemID
) t3
on (t1.ItemID = t3.ItemID
and t1.StatusDate = t3.StatusDate)
order by
t1.ItemID
drop table #TestTable
January 9, 2008 at 12:52 pm
Thank You!
That was quick and it works!
🙂
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply