Join two tables to retrieve max date

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 2005 Forums

Transact-SQL (2005)

Join two tables to retrieve max date

Author

Topic

naveenbalraj
Starting Member

18 Posts

Posted - 2009-05-21 : 12:25:29

Hi All,

I have this results joining 2 tables tbl1 and tbl2.

date (tbl1) ID(tbl2) description(tbl2)
1/4/2005 12 xx
1/4/2005 13 yy
4/6/2005 14 zz
4/6/2005 14 aa
4/6/2005 14 bb
4/6/2005 14 cc
4/6/2005 14 bb
7/24/2006 15 dd
2/27/2006 15 yy
2/13/2006 15 aa
11/30/2005 15 vv
10/18/2005 15 rr

SELECT tbl1.date ,tbl2.ID, tbl2.description
FROM tbl1 INNER JOIN
tbl2 ON tbl2.ID = tbl1.ID

Now, I need only the rows with max date against each ID.
The result I needed

date (tbl1) ID(tbl2) description(tbl2)
1/4/2005 12 xx
1/4/2005 13 yy
4/6/2005 14 zz
7/24/2006 15 dd

Any help is greatly appreciated.

-NB

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-05-21 : 12:29:47

select date, id, description from (
SELECT tbl1.date ,tbl2.ID, tbl2.description, row_number() Over (partition by tbl1.id order by tbl1.date desc) as recid
FROM tbl1 INNER JOIN
tbl2 ON tbl2.ID = tbl1.ID) as d
where recid = 1

E 12°55'05.63"
N 56°04'39.26"

naveenbalraj
Starting Member

18 Posts

Posted - 2009-05-21 : 12:35:39

it says row_number() is not a function

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-05-21 : 12:36:45

1. You will need Microsoft SQL Server 2005 (I guess this is checked since you posted in a 2005 forum)
2. You will need to set compatibility level to 90 or higher.

E 12°55'05.63"
N 56°04'39.26"

naveenbalraj
Starting Member

18 Posts

Posted - 2009-05-21 : 12:41:08

my mistake, I will repost in sql 2000
thanks for ur help

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources