INNER JOIN and last entry - SQL Server Forums

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

Transact-SQL (2008)

INNER JOIN and last entry

Author

Topic

bonekrusher
Starting Member

44 Posts

Posted - 2010-09-01 : 08:04:57

Hi,

I have two tables:

tbl_tm
tbl_comments

I want to show the distinct value of tbl_tm.tm_number and show the last entry for that in table tbl_comments.

e.g.

tbl_tm.tm_number = tbl_comments.tbl_comments_tm_number

How can I do this?

SELECT     tbl_tm.tm_number, tbl_comments.tbl_comments_comments
FROM         tbl_tm INNER JOIN
                      tbl_comments ON tbl_tm.tm_number = tbl_comments.tbl_comments_tm_number

Thanks,

Bones

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2010-09-01 : 08:13:01

post sample date and expected output

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/

bonekrusher
Starting Member

44 Posts

Posted - 2010-09-01 : 08:28:46

Hi,

tbl_tm


tbl_tm_id | tm_number
----------------------
1         | TM1
2         | TM2
3         | TM3

tbl_comments


tbl_comments_id | tbl_comments_tm_number | tbl_comments_comments
----------------------------------------------------------------
1               | TM3                    | 1st comment on TM3
2               | TM3                    | 2nd comment on TM3
3               | TM2                    | 1st comment on TM2
4               | TM1                    | 1st comment on TM1
5               | TM3                    | 3rd comment on TM3
6               | TM2                    | 2nd comment on TM2
7               | TM2                    | 3rd comment on TM2

Desired output:


tbl_tm_id | tm_number | last_comment
----------------------------------------
1         | TM1       | 1st comment on TM1
2         | TM2       | 3rd comment on TM2
3         | TM3       | 3rd comment on TM3

thanks,

Bones

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2010-09-01 : 08:44:43

Try this...

Select b.tbl_tm_id, a.tbl_comments_tm_number,c.tbl_comments_comments from
(Select max(tbl_comments_id) as tbl_comments_id,tbl_comments_tm_number from tbl_comments
group by tbl_comments_tm_number) a
inner join tbl_tm b on a.tbl_comments_tm_number=b.tbl_tm_id
inner join tbl_comments c on c.tbl_comments_id=a.tbl_comments_id and a.tbl_comments_tm_number =c.tbl_comments_tm_number

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/

Ifor
Aged Yak Warrior

700 Posts

Posted - 2010-09-01 : 08:59:57

Using ROW_NUMBER() will probably be more efficent in SQL2008


-- *** Create some usable test data ***
-- Do this if you want quick answers!
CREATE TABLE #tbl_tm
(
	tbl_tm_id int NOT NULL
	,tm_number varchar(25) NOT NULL
)
INSERT INTO #tbl_tm
VALUES (1, 'TM1')
	,(2, 'TM2')
	,(3, 'TM3')

CREATE TABLE #tbl_comments
(
	tbl_comments_id int NOT NULL
	,tbl_comments_tm_number varchar(25) NOT NULL
	,tbl_comments_comments varchar(50) NOT NULL
)
INSERT INTO #tbl_comments
VALUES (1, 'TM3', '1st comment on TM3')
	,(2, 'TM3', '2nd comment on TM3')
	,(3, 'TM2', '1st comment on TM2')
	,(4, 'TM1', '1st comment on TM1')
	,(5, 'TM3', '3rd comment on TM3')
	,(6, 'TM2', '2nd comment on TM2')
	,(7 , 'TM2', '3rd comment on TM2')
-- *** End Of Test Data ***

SELECT *
FROM #tbl_tm T1
	JOIN
	(
		SELECT *
			,ROW_NUMBER() OVER (PARTITION BY T2.tbl_comments_tm_number ORDER BY tbl_comments_id DESC) AS RowNum
		FROM #tbl_comments T2
	
	) D
		ON T1.tm_number = D.tbl_comments_tm_number
			AND D.RowNum = 1

bonekrusher
Starting Member

44 Posts

Posted - 2010-09-01 : 09:09:28

Thanks very much. With a little tweaking, it worked. Thanks again.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources