November 20, 2007 at 9:10 am
First off, I really like this site and have enjoyed many of the articles and forum posts. This is my first post: I am trying to rewrite a query to make it more concise, and I am specifically trying to remove a subquery.
Here's the problem. I have a table of profiles. Each profile has a ProfileID, PeopleID, and a LastUpdated field. Each person may have multiple profiles in this table. I want to select the most recent Profile for each person.
Currently I'm doing that this way:
Select
(select
top 1 ProfileID
from Profiles
where Profiles.PeopleID=PeopleIDs.PeopleID
ORDER BY Updated Desc)
FROM
(select distinct PeopleID from Profiles) as PeopleIDs
What do you guys think? I'd really like to do this without subqueries, as I need to join this with quite a few other tables and its starting to get pretty ugly looking. So far I've tried to use Group By and Top to no success.
November 20, 2007 at 9:29 am
SELECT p.PeopleID, p.ProfileID
FROM Profiles p JOIN
(SELECT PeopleID, MAX(Updated) LastUpdated
FROM Profiles
GROUP BY PeopleID) x ON p.PeopleID = x.PeopleID AND p.Updated = x.LastUpdated
November 20, 2007 at 9:37 am
We do this sort of query quite a lot. We've found that we use one of two patterns, one works well for a single row, one works well for multiple rows.
For multiple rows:
SELECT...
FROM Table1 t1
JOIN VersionTable vt
ON t1.Id = vt.Id
AND vt.Version = (SELECT TOP(1) Version FROM VersionTable vt2
WHERE vt2.Id = t1.Id
ORDER BY vt2.Version DESC)
For a single row:
SELECT...
FROM Table1 t1
CROSS APPLY (SELECT TOP(1) [columns needed]
FROM VersionTable v
WHERE v.Id = t1.Id
ORDER BY v.Version DESC) AS v
We've also found that combining this with making sure that the primary key on all the versioned tables is compound a clustered consisting of the key of the ID and the Version. If you go that route, you need to add the ID to the Order by statements to be sure it always results in a clustered index seek.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
November 20, 2007 at 10:42 am
Thanks for all the ideas! I ended up going with the cross apply in this case, and it works great.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply