Joining top records in T-SQL

Question

SELECT MD.*, Contact.FirstName 
FROM MerchantData MD
JOIN Merchant M ON M.MerchID = MD.MerchID   
JOIN (SELECT TOP 1 * FROM Location WHERE Location.BusID = MD.BusID) L ON L.BusID=MD.BusID
AND L.Deleted = 0
JOIN Contact ON Contact.ContactID = L.PrincipalID

I am using SQLSERVER 2008 and trying to write this SQL statement. There is some times multiple locations for a busid and I want to join in only the first found. I am getting an error on the part "Location.BusID = MD.BusID" as MD.BusID cannot be bound. Is it possible to use the MD table in the nested select statment in this join or is there another way of accomplishing this?

I am contiplating putting the data using nested querys in the column list to grab the contact data driectly there.

JNK · Accepted Answer · 2011-08-10T19:43:27.540

9

It would be simpler I think to have a subquery of the full result set:

SELECT MD.*, Contact.FirstName 
FROM MerchantData MD
JOIN Merchant M ON M.MerchID = MD.MerchID   
JOIN (SELECT BusID, MAX(PrincipalID)
      FROM Location
      WHERE Deleted = 0
      GROUP BY BusID) L ON L.BusID=MD.BusID
JOIN Contact ON Contact.ContactID = L.PrincipalID

You still get one record per BusID in the JOIN but it's not correlated.

edited Aug 10 '11 at 19:43

answered Aug 10 '11 at 16:48

JNK

63,321
15
122
138

problem there is MAX is not the same as first – paparazzo Aug 10 '11 at 18:51
1

He only wants one `BusID` and didn't specify a sort order. It's trivial to add a sort criteria if he chooses. – JNK Aug 10 '11 at 18:53
@JNK problem is that asking for max deleted will not work in this case because of the L.Deleted = 0 after the subselect – t-clausen.dk Aug 10 '11 at 19:36
@t-clausen - good catch, I'll correct the query. We can put that condition on the inner select. – JNK Aug 10 '11 at 19:42
The question has the same problem *shug*. Seems you just inherited it. – t-clausen.dk Aug 10 '11 at 20:57

score 4 · Answer 2 · answered Aug 10 '11 at 17:12

4

SELECT MD.*, Contact.FirstName  
FROM MerchantData MD 
JOIN Merchant M ON M.MerchID = MD.MerchID    
CROSS APPLY (SELECT TOP 1 * FROM Location WHERE BusID = MD.BusID AND DELETED = 0) L
JOIN Contact ON Contact.ContactID = L.PrincipalID

answered Aug 10 '11 at 17:12

t-clausen.dk

43,517
12
59
92

score 2 · Answer 3 · edited May 23 '17 at 10:28

This is a case of the "top n per group" problem. This question will guide you:

SQL Server query select 1 from each sub-group

You'll want to be doing something like this:

SELECT MD.* ,
       Contact.FirstName 
FROM MerchantData MD
JOIN Merchant     M ON M.MerchID = MD.MerchID   
JOIN ( select * ,
              seq = rank() over( partition by BusID order by BusID , ... )
       from Location
       where L.Deleted = 0
     ) L on L.BusID = MD.BusID
        and seq     = 1
JOIN Contact ON Contact.ContactID = L.PrincipalID

The virtual table expression should return at most 1 Location per BusID (0 if the BusID has no non-deleted Locations).

The stated requirement is to look at the first and then see if it is deleted which is not the same as first deleted. But your solution is elegant and you do state clearly what it does. — paparazzo, Aug 10 '11 at 19:14

paparazzo · Answer 4 · 2011-08-10T19:55:50.977

To try and isolate out the error I would try. See if it can match Location.BusID = MD.BusID.

    SELECT MD.*, Contact.FirstName 
    FROM MerchantData MD
    JOIN Merchant M ON M.MerchID = MD.MerchID   
    JOIN Location On Location.BusID = MD.BusID

You do not use the * so use

    SELECT TOP 1 Location.BusID FROM Location WHERE Location.BusID = MD.BusID

Once you get the syntax working.

You do know that once you get this working it will only match if the "first" row and then check if it is deleted. The problem is that without an order by the "first" row is arbitrary. Even with a clustered index on a table there is no guaranteed sort without an order by clause. To get a repeatable answer you need a sort. But if you are sorting and only want the top row then a MAX or MIN and a group be more straight forward.

If you want just business that have one or more deleted locations then the following should work but you need to break out the columns for the group by. If two deleted locations have differenct contact name then it will report each. So, this may not be what you are looking for.

    SELECT MD.col1, MD.col2, Contact.FirstName 
    FROM MerchantData MD
    JOIN Merchant M ON M.MerchID = MD.MerchID   
    JOIN Location L
       ON  L.BusID = MD.BusID
       AND L.Deleted = 0
    JOIN Contact ON Contact.ContactID = L.PrincipalID 
    GROUP BY MD.col1, MD.col2, Contact.FirstName

Joining top records in T-SQL

4 Answers4