Getting recent date

[sarakmo]

Greetings,

I'm trying to retrieve the most recent date for each code.

for example:
Table A
code Date
code111: 02/10/2005
code111: 03/10/2006
code111: 03/10/2007
code122: 04/10/2000
code122: 03/10/2003
code122: 03/10/2007

I'm trying to retrieve the most recent date for each code and use it in a large query.

Right now I've tried dumping my MAX(date) results in a temp table...but obviously the 'max' date in this case returns the only date it has.

Please help!

Appreciate it greatly. []

 

[SQLDenis]

select * from tableA a
join(
select code,max(date) as MaxDate
from tablea
group by code) z on a.code =z.code
and a.date = z.MaxDate

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions

 

[gmmastros]

Can you show us the code you have so far? Without knowing for sure, I would guess that you are missing the group by clause in your query.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[LNBruno]

select code, max(codeDate)
from #test
group by code

< M!ke >
[small]I can say nothing, which is cowardly, I can lie, which is immoral, or I can tell the truth, which will upset people. - Tiki Barber[/small]