Need set based solution - SQL Server Forums

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)

Need set based solution

Author

Topic

rupspawar
Starting Member

3 Posts

Posted - 2006-11-16 : 06:06:06

Create Table #tbGtrade (CId INT, TDate Datetime, Grade varchar(1))

insert into #tbGtrade
select 217 as CompanyId , '2006-11-09 00:00:00.000' as tradedate ,'B' as Recomm
union
select 217 as CompanyId , '2006-10-09 00:00:00.000' as tradedate ,'s' as Recomm
union
select 221 as CompanyId , '2006-10-10 00:00:00.000' as tradedate ,'b' as Recomm
union
select 337 as CompanyId , '2006-10-10 00:00:00.000' as tradedate ,'b' as Recomm

select * from #tbGtrade

CId Date Grade
217 2006-11-09 00:00:00.000 B
221 2006-10-10 00:00:00.000 b
337 2006-10-10 00:00:00.000 b
217 2006-10-09 00:00:00.000 s

I tried using following query

SELECT CId , MAX(TDate) AS [Date], MAX(Grade)as grade
FROM #tbGtrade
group by CId

Desired Results
CId Date Grade
217 2006-11-09 00:00:00.000 B
221 2006-10-10 00:00:00.000 b
337 2006-10-10 00:00:00.000 b

basically, I want one a corresponding grade for max date of each CId.

Thanku All for your help and time.
Rupesh..

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-11-16 : 06:35:55

How about this?

select CID, tdate, Grade
from #tbGtrade t1
where tdate = (select max(tdate) from #tbGtrade t2 where t2.CID = t1.CID)
order by CID

Harsh Athalye
India.
"Nothing is Impossible"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources