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

Transact-SQL (2000)

Join two tables to retrieve max date

Author

Topic

naveenbalraj
Starting Member

18 Posts

Posted - 2009-05-21 : 12:40:23

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

mualsh
Starting Member

8 Posts

Posted - 2009-05-21 : 13:36:24

Do you want to share the table structures of tbl1 and tbl2 along with some sample data for these two tables?

naveenbalraj
Starting Member

18 Posts

Posted - 2009-05-21 : 16:54:25

tbl1

id pk,int
date

tbl2
id1 pk,int
id fk
description

table smaples
tbl1
id date
12 1/4/2005
13 1/4/2005
14 4/6/2005
14 4/6/2005
14 4/6/2005
14 4/6/2005
14 4/6/2005
15 7/24/2006
15 2/27/2006
15 2/13/2006
15 11/30/2005
15 10/18/2005

tbl2
id1 id description
1 12 xx
2 13 yy
3 14 zz
4 14 aa
5 14 bb
6 14 cc
7 14 bb
8 15 dd
9 15 yy
10 15 aa
11 15 vv
12 15 rr

does this help

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-05-21 : 18:12:43

[code]
SELECT tbl1.date ,tbl2.ID, tbl2.description
FROM tbl1
INNER JOIN tbl2 ON tbl2.ID = tbl1.ID
INNER JOIN
(
SELECT date = max(tbl1.date) ,tbl2.ID
FROM tbl1
INNER JOIN tbl2 ON tbl2.ID = tbl1.ID
GROUP BY tbl2.ID
) m on tbl1.date = m.date AND tbl2.ID = m.ID
[/code]

KH
[spoiler]Time is always against us[/spoiler]

mualsh
Starting Member

8 Posts

Posted - 2009-05-21 : 19:26:50

The whole idea of a primary key is to enable you to uniquely identify a row in a table. With that being said, I am not sure how your table tbl1 (with 'id' as its primary key) is letting you insert multiple rows (5 rows for id = 14). Can you please clarify?

naveenbalraj
Starting Member

18 Posts

Posted - 2009-05-22 : 14:40:17

thanks khtan. It helped.
mualsh, please review his suggestion.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources