Help needed with this SQL Query

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

Help needed with this SQL Query

Author

Topic

arvindkumar09
Starting Member

2 Posts

Posted - 2007-02-05 : 12:29:37

I have a table with the following columns
Id, title, year,dateedited with id being the primary key...

typically rows look like this

1 test 2000 1/1/2006
2 test2 2002 3/1/2006
3 test3 2004 4/1/2006
4 test4 2000 5/1/2006

how do i retrive the latest entry for each year as in something like this

4 test4 2000 5/1/2006
2 test2 2002 3/1/2006
3 test3 2004 4/1/2006

Please help me with the query

nr
SQLTeam MVY

12543 Posts

Posted - 2007-02-05 : 12:32:01

select *
from tbl t1 where dte = (select max(dte) from tbl t2 where t2.year = t1.year)

or you could
select t1.*
from tbl t1
join (select year, dte = max(dte) from tbl group by year) t2
on t1.year = t2.year
and t1.dte = t2.dte

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

arvindkumar09
Starting Member

2 Posts

Posted - 2007-02-05 : 13:36:21

Thanks !

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources