query join - 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)

query join

Author

Topic

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2007-11-26 : 04:09:43

I have a table called placements and a table called participants

each participant can have many placement records (in the placements table there is an id called participantid)

how can i query all the participants with just the last placement record (ordered by placementid desc)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2007-11-26 : 04:18:18

SELECT *
FROM participants prts
INNER JOIN placements pmts
ON pmts.participantid= prts.participantid
INNER JOIN (SELECT MAX(placementid) AS 'latest_placement_id',
participantid
FROM placements
GROUP BY participantid) max_prts
ON max_prts.participantid=prts.participantid
AND pmts.placementid=max_prts.latest_placement_id

suresha_b
Yak Posting Veteran

82 Posts

Posted - 2007-11-26 : 04:39:27

select a.ParticipantId, (select top 1 b.PlacementId from dbo.Placements b
where b.ParticipantId = a.ParticipantId
order by PlacementId desc
) as PlacementId
from dbo.Participants a

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2007-11-27 : 07:00:21

both of these are not working

any other ideas?

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2007-11-27 : 07:06:06

how about scripting some sample data etc. so we can see what result you'd expect to be the right one? it's obviously not clear from your original post

Em

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2007-11-27 : 09:04:18

participants

participantid
firstname
lastname
address
city
state
zip

placements

placementid
participantid
location
phone
fax

I want to show all a list of placement records but only the last one for each participant (by placementid desc)

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2007-11-27 : 09:04:59

RobertParker
Starting Member

10 Posts

Posted - 2007-11-27 : 11:04:37

I was having the exact same problem with a query yesterday, and I got it working after reading this thread. I used suresha_b's solution.

So there must be a problem with how you are implementing it. Post your code.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources