JOIN Query that returns only the first record from every joined second table

Posted on 2006-05-10
Medium Priority
Last Modified: 2006-11-18

Here's my problem: I have two tables.


recID       Description
a             record a
b             record b
c             record c
d             record d

recID       Table1RecID       Description
1             a                       Record 1
2             a                       Record 2
3             a                       Record 3
4             b                       Record 4
5             b                       Record 5

I would like to make a left join query, that would return ONLY ONE (can be random) record for every TABLE1 record FROM TABLE 2. Example:

recIDTable1         RecIDTable2        DescTable2
a                         2                       Record2
b                         4                       Record4
Question by:coyotee
LVL 17

Expert Comment

ID: 16646761
Try this!

Select Top  1 Table1 .recID,   Table2.RecID,  Table2.Desc From Table1, Table2
order by newid() --this for random
LVL 11

Accepted Solution

Otana earned 2000 total points
ID: 16646766
Try this:

from Table1 t1
left join (select Table1RecID, max(Description) from Table2 group by Table1RecID) t2 on t1.recID = t2.Table1RecID

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Viewers will learn how the fundamental information of how to create a table.
Suggested Courses

807 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question