sql query help - 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 2005 Forums

Transact-SQL (2005)

sql query help

Author

Topic

DesiGal
Starting Member

31 Posts

Posted - 2009-09-09 : 15:05:05

Suppose i have 3 tables

Animal
-AnimalId
-AnimalName

Animal_Comment
-AnimalId
-CommentId

Comment
-CommentId
-Comment
-CreatedAt

A single animal can have multiple comments

My SQL query is

select Animal.AnimalId,Animal.AnimalName,Comment.Comment
inner join Animal_Comment AC on AC.AnimalId=Animal.AnimalId
inner join Comment C on C.CommentId=AC.CommentId

Is it possible to have a query which will give me a single latest comment for the animal

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-09-09 : 15:16:52

Here's one way:


select a.AnimalId
       ,a.AnimalName
       ,c.Comment
from   animal a
outer apply (
       select top 1 c.comment
       from   Animal_Comment ac
       inner join Comment C on C.CommentId = ac.CommentId
       where  ac.AnimalId = a.AnimalId
       order by c.createdAt desc
       ) c

Be One with the Optimizer
TG

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-09-10 : 02:54:28

Other possible ways
http://sqlblogcasts.com/blogs/madhivanan/archive/2008/09/12/return-top-n-rows.aspx

Madhivanan

Failing to plan is Planning to fail

DesiGal
Starting Member

31 Posts

Posted - 2009-09-10 : 11:45:43

Thanks for all your replies.I will try them and let you know the results

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources