INNER JOIN on TOP 1

Question

Post reply

INNER JOIN on TOP 1

Simon E Doubt

SSCarpal Tunnel

Points: 4191
More actions
June 24, 2008 at 8:10 am

#381353

Good Morning, everyone:
I'm trying to join two tables.
However, I only want to return the TOP 1 row from the child table.
As a simplified example, consider the following two tables (I've included the code to create the temp tables and data below):
TableA
=====
RetailerID
54
33
TableB
=====
RetailerID, RetailerIDTheirs
54, 'ABC'
54, 'DEF'
33, 'TUV'
33, 'WXYZ'
I would like to know how to write the JOIN syntax to return only the TOP 1 row from TableB.
The only solution that I know how to do is something like this:
SELECT
A.RetailerID,
(SELECT TOP 1 RetailerIDTheirs FROM TableB WHERE RetailerID = A.RetailerID ORDER BY RetailerIDTheirs ASC) AS RetailerIDTheirs
FROM TableA AS A
Which returns exactly what I want:
This works fine, except that when I try to group the data, I get an error message telling me:
"Cannot use an aggregate or a subquery in an expression used for the group by list of a GROUP BY clause."
For the purpose of keeping things simple, I've left-out the part of the code that includes the grouping.
As always, thanks to everyone for any thoughts/input.
-Simon
Code for creating sample tables and rows:
-- Create TableA, the Parent table
CREATE TABLE TableA
(RetailerID INT NOT NULL PRIMARY KEY CLUSTERED)
-- Populate TableA
INSERT TableA (RetailerID)
VALUES (54)
INSERT TableA (RetailerID)
VALUES (33)
-- Create TableB, the Child table
CREATE TABLE TableB
(
RetailerID INT NOT NULL
CONSTRAINT FK_TableB_RetailerID FOREIGN KEY ([RetailerID]) REFERENCES TableA ([RetailerID]),
RetailerIDTheirs VARCHAR(10) NOT NULL
)
-- Populate TableB
INSERT TableB (RetailerID, RetailerIDTheirs)
VALUES (54, 'ABC')
INSERT TableB (RetailerID, RetailerIDTheirs)
VALUES (54, 'DEF')
INSERT TableB (RetailerID, RetailerIDTheirs)
VALUES (33, 'TUV')
INSERT TableB (RetailerID, RetailerIDTheirs)
VALUES (33, 'WXYZ')

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 1

Hi Simon

This is simple but somewhat restrictive:

SELECT A.RetailerID, MIN(B.RetailerIDTheirs) AS RetailerIDTheirs

FROM #TableA A

LEFT JOIN #TableB B ON B.RetailerID = A.RetailerID

GROUP BY A.RetailerID

This is less simple but offers more flexibility:

SELECT A.RetailerID, B2.*

FROM #TableA A

LEFT JOIN (SELECT RetailerID, MIN(RetailerIDTheirs) AS RetailerIDTheirs FROM #TableB GROUP BY RetailerID) B ON B.RetailerID = A.RetailerID

LEFT JOIN #TableB B2 ON B2.RetailerID = B.RetailerID AND B2.RetailerIDTheirs = B.RetailerIDTheirs

Both are likely to be more efficient than a correlated subquery.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Mark Cowne One Orange Chip Points: 26952 More actions · Answer 2

Another alternative

SELECT

A.RetailerID,

X.RetailerIDTheirs

FROM TableA AS A

CROSS APPLY (SELECT TOP 1 B.RetailerIDTheirs FROM TableB B WHERE B.RetailerID = A.RetailerID ORDER BY B.RetailerIDTheirs ASC) AS X

____________________________________________________

Deja View - The strange feeling that somewhere, sometime you've optimised this query before

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537

Simon E Doubt SSCarpal Tunnel Points: 4191 More actions · Answer 3

Mark and Chris,

Thank you so much for your replies.

I'll try both and let you know what works best.

Thanks again - I really appreciate it.

-Simon

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 4

Mark (6/24/2008)
Another alternative
SELECT
A.RetailerID,
X.RetailerIDTheirs
FROM TableA AS A
CROSS APPLY (SELECT TOP 1 B.RetailerIDTheirs FROM TableB B WHERE B.RetailerID = A.RetailerID ORDER BY B.RetailerIDTheirs ASC) AS X

Nice one, Mark, and far closer to the OP's requirements. We're still on 2k here.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Grant Fritchey SSC Guru Points: 398882 More actions · Answer 5

This will work with 2000:

SELECT

A.RetailerID,

b.RetailerIDTheirs

FROM TableA AS A

LEFT JOIN TableB AS B

ON A.RetailerID = B.RetailerID

AND b.RetailerIDTheirs = (SELECT TOP 1 B2.RetailerIDTheirs

FROM TableB B2

WHERE B2.RetailerID = A.RetailerID

ORDER BY B2.RetailerIDTheirs ASC)

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Simon E Doubt SSCarpal Tunnel Points: 4191 More actions · Answer 6

Just as a follow up, Mark I used your CROSS APPLY syntax and it worked perfectly - allowing me all the groupings I needed.

Thanks again everyone for the expert help.

-Simon

Hari.Sharma SSCertifiable Points: 6904 More actions · Answer 7

Dear Friend,

I think the simplest query for your question is as mentioned below:

-------------------------------------------------------------

Select T2.RetailerID, T2.RetailerIDTheirs

From TableA T1

Inner Join

(Select *,Rank() Over (Partition By RetailerID Order by RetailerIDTheirs) As Rank from TableB) T2

ON T1.RetailerID=T2.RetailerID

Where Rank=1

Cheers,
Hari
Tips & Tricks for SQL BI Developers