Max Date querey help 1

[daguas]

SQL 2005

Weak on SQL, need assistance on creating a query. I have a table that contains header information for monthly cost forecast information for numerous projects. Each month a new cost forecast for each project will be entered into this table and later approved to report on. A simplified examaple of the table is as follows:

ForecastID ProjectID IsApproved ApprovedDate
1 1 True 1/1/2011
2 2 True 1/1/2011
3 3 True 1/1/2011
4 1 False NULL
5 2 True 2/1/2011
6 3 True 2/1/2011
7 1 False NULL

I need a query to return ForecastID's 5 and 6. I know it must be simple, but I weak on SQL.

Thanks

 

[imex]

Try something like that:

select * from MyTable 
where 
ApprovedDate = 
(select max(ApprovedDate) from MyTable)

I hope this helps.


Casas a venda em Suzano

 

[daguas]

Thanks for the reply imex. Its almost what I'm looking for. This only returns 1 row though. I need to get 1 row for each ProjectID based on the max(ApprovedDate) for each projectid. If that makes sense.

 

[imex]

So try something like that:

select * from MyTable T
where 
ApprovedDate = 
(select max(ApprovedDate) from MyTable where ProjectID = T.ProjectID)

I hope this helps.

 

[daguas]

That did it, Thanks so much.