TOP 1 on outer join by SELECT - 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)

TOP 1 on outer join by SELECT

Author

Topic

cpirie
Starting Member

2 Posts

Posted - 2010-02-09 : 14:29:41

I have a feeling it's my lack of knowledge of terms that's stopping google from answering this, so here's a forum post. :)

For simplicity, I have 2 tables in a one to many. What I'm attempting to do is get a list of all records in Table A (list of people), and the most recent document in table B (list of documents) if there is one at all.

The sql statement below is about as close as I've gotten to the goal so far, but unfortunately with the TOP 1 involved in the outer join query makes it only return 1 result for all records returned from the left side of the query.

Is there a way to get the right side of the join to execute for every record on the left side?

SELECT p.ResourceID, d.ExpiryDate
FROM People p
LEFT OUTER JOIN (SELECT TOP 1 ExpiryDate, ResourceID
                 FROM Documents
                 ORDER BY ExpiryDate DESC) d
ON p.ResourceID = d.ResourceID

Results with the query are currently:
57, 1/1/2010
69, NULL
80, NULL
120, NULL
134, NULL

When the results I'm going for should be like:
57, 1/1/2010
69, 4/26/2009
80, NULL
120, 3/8/2006
134, 9/28/1999

Any help would be greatly appreciated!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-09 : 14:35:21

[code]SELECT p.ResourceID, d.ExpiryDate
FROM People p
OUTER APPLY (SELECT TOP 1 ExpiryDate
FROM Documents
WHERE ResourceID = p.ResourceID
ORDER BY ExpiryDate DESC) d
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2010-02-09 : 14:45:53

[code]SELECT p.ResourceID, d.ExpiryDate
FROM People p
LEFT OUTER JOIN
(
SELECT MAX(ExpiryDate) AS ExpiryDate, ResourceID
FROM Documents
GROUP BY ResourceID
) d
ON p.ResourceID = d.ResourceID[/code]

cpirie
Starting Member

2 Posts

Posted - 2010-02-09 : 15:22:58

Works like a charm! Thanks a bunch vishakh16!

quote:
Originally posted by visakh16

SELECT p.ResourceID, d.ExpiryDate
FROM People p
OUTER APPLY (SELECT TOP 1 ExpiryDate
FROM Documents
WHERE ResourceID = p.ResourceID
ORDER BY ExpiryDate DESC) d

------------------------------------------------------------------------------------------------------
SQL Server MVP

ms65g
Constraint Violating Yak Guru

497 Posts

Posted - 2010-02-09 : 15:25:05

[code]SELECT p.ResourceID, MAX(ExpiryDate)
FROM People p
JOIN Documents d
ON p.ResourceID = d.ResourceID
GROUP BY p.ResourceID
UNION ALL
SELECT p.ResourceID, NULL
FROM People p
WHERE NOT EXISTS
(SELECT *
FROM Documents d
WHERE p.ResourceID = d.ResourceID);

[/code]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-10 : 01:25:16

quote:
Originally posted by cpirie

Works like a charm! Thanks a bunch vishakh16!

quote:
Originally posted by visakh16

SELECT p.ResourceID, d.ExpiryDate
FROM People p
OUTER APPLY (SELECT TOP 1 ExpiryDate
FROM Documents
WHERE ResourceID = p.ResourceID
ORDER BY ExpiryDate DESC) d

------------------------------------------------------------------------------------------------------
SQL Server MVP

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources