How to Join to First Row

How to Join to first row

SELECT   Orders.OrderNumber, LineItems.Quantity, LineItems.Description
FROM     Orders
JOIN     LineItems
ON       LineItems.LineItemGUID =
         (
         SELECT  TOP 1 LineItemGUID 
         FROM    LineItems
         WHERE   OrderID = Orders.OrderID
         )

In SQL Server 2005 and above, you could just replace INNER JOIN with CROSS APPLY:

SELECT  Orders.OrderNumber, LineItems2.Quantity, LineItems2.Description
FROM    Orders
CROSS APPLY
        (
        SELECT  TOP 1 LineItems.Quantity, LineItems.Description
        FROM    LineItems
        WHERE   LineItems.OrderID = Orders.OrderID
        ) LineItems2

Please note that TOP 1 without ORDER BY is not deterministic: this query you will get you one line item per order, but it is not defined which one will it be.

Multiple invocations of the query can give you different line items for the same order, even if the underlying did not change.

If you want deterministic order, you should add an ORDER BY clause to the innermost query.

Example sqlfiddle

How to select first row from a join that returns multiple rows on the primary key

One method uses a subquery:

select ep.personname, em.*
from employee ep
cross apply (
    select top (1) em.email 
    from email em   
    where em.employeeid = ep.id 
    order by em.email
) em

If you just want the email and nothing else, aggregation is also OK:

select ep.personname, 
    (select min(em.email) from email em where em.employeeid = ep.id) as email
from employee ep

Or:

select ep.personname, min(em.email) as email
from employee ep
left join email em on em.employeeid = ep.id
group by ep.id, ep.personname

How to left join to first row in SQL Server

Following the comment of t-clausen.dk this does the job:

change CROSS APPLY to OUTER APPLY

How to left join with first matching row and fill the rest with null?

Thanks to @Akina I can provide the final version of code for my example:

SELECT name,
   animals.color,
   places.place,
   places.amount amount_in_place,
   CASE WHEN name = LAG(name) OVER (PARTITION BY name ORDER BY place)
   THEN 
      null
   ELSE 
      (SELECT GROUP_CONCAT("Amount: ",amount, " and price: ",price 
      SEPARATOR ", ") AS sales 
      FROM in_sale 
      WHERE in_sale.name=animals.name GROUP BY name) 
   END sales
FROM animals
LEFT JOIN places USING (name)
LEFT JOIN in_sale USING (name)
GROUP BY 1,2,3,4;

Note that it works only for MySQL version 8 or higher.

For older versions we can use self-defined variable:

SELECT x.*,
   @rowname,
   CASE WHEN name = @rowname
   THEN 
      null
   ELSE 
      (SELECT GROUP_CONCAT('Amount: ',amount, ' and price: ',price 
      SEPARATOR ', ') AS sales 
      FROM in_sale 
      WHERE in_sale.name=x.name GROUP BY name) 
   END sales,
   @rowname := name
   from
(SELECT name,
   animals.color,
   places.place,
   places.amount amount_in_place
FROM animals
LEFT JOIN places USING (name)
LEFT JOIN in_sale USING (name)
GROUP BY 1,2,3,4) as x
join (SELECT @rowname := 0) as r;

WARNING! As @philipxy pointed out in a comment, it can give very different and unexpected results. For me, comparing results in columns @rowname and @rowname := name and checking the sales column, works fine every time. (locally 10.4.11-MariaDB and on an external server MySQL 5.7.34-37-log - Percona Server - I'm joining over a dozen tables. It's returning over 20000 rows)

Select the first row of a LEFT JOIN

If you just want the latest start_date per reference, you can use aggregation:

select c.reference, max(cp.start_date) max_start_date
from contracts c
left join contracts_premiums cp on cp.contract_id = c.id
group by c.reference

This guarantees that you will only get one row per reference.

If you want more columns from contracts_premiums, or if you want to sort by a column other than start_date (possibly, you want created_at instead), then another option is distinct on:

select distinct on (c.reference) c.reference, cp.start_date, cp.created_at
from contracts c
left join contracts_premiums cp on cp.contract_id = c.cid
order by c.reference, cp.created_at desc

LEFT JOIN only first row

@Matt Dodges answer put me on the right track. Thanks again for all the answers, which helped a lot of guys in the mean time. Got it working like this:

SELECT *
FROM feeds f
LEFT JOIN artists a ON a.artist_id = (
    SELECT artist_id
    FROM feeds_artists fa 
    WHERE fa.feed_id = f.id
    LIMIT 1
)
WHERE f.id = '13815'

SQL LEFT JOIN first row only

Wrapping it in another query does the trick?

SELECT RequiredId, <all_the_other_fields> from (
SELECT t2.SomeId AS RequiredId
-- ...other data mainly from t2
FROM DataTable1 AS t1
   LEFT JOIN DataTable2 AS t2 
   ON t2.OtherId = t1.ExperienceId 
   AND t2.LanguageId = 
      (SELECT TOP 1 t1.LanguageId
      ORDER BY t1.LanguageId)
   ) group by RequiredId, <all_the_other_fields> 

or even not extracting the column in the first place?

SELECT distinct t2.SomeId AS RequiredId
-- ...other data mainly from t2 BUT not the Language id
FROM DataTable1 AS t1
   LEFT JOIN DataTable2 AS t2 
   ON t2.OtherId = t1.ExperienceId 
   AND t2.LanguageId = 
      (SELECT TOP 1 t1.LanguageId
      ORDER BY t1.LanguageId)

Only select the first row from another joined table

You seem to want the latest message per lab. One option uses a lateral join. Assuming that table messages has a foreign key column called labid that refers to labs, and a column called id that can be used to order the rows, you would phrase this as:

select l.*, m.mesage
from labs l 
cross apply (
    select top (1) * from messages m where m.labid = l.labid order by m.id desc
) m

If you want to allow labs that have no messages, use outer apply instead.

Another option is row_number():

select l.*, m.mesage
from labs l 
inner join (
    select m.*, row_number() over(partition by labid order by id desc) rn
    from messages m
) m on m.labid = l.labid and m.rn = 1

---

Related Topics