Select The First Row in a Join of Two Tables in One Statement

Select the first row from two tables

Just this:

SELECT *
FROM Database.TableX, Database.TableY
LIMIT 1

or maybe this?

SELECT *
FROM
  (SELECT * FROM Database.TableX LIMIT 1) t1,
  (SELECT * FROM Database.TableY LIMIT 1) t2

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 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 get the top 1 row from second table while joining two tables : SQL Server 2014

You can use cross apply. The conditions are a little unclear, but the idea is:

select t1.*, t2.images
from table1 t1 cross apply
     (select top (1) t2.*
      from t2
      where t2.? = t1.id
     ) t2;

I would speculate that the correlated condition should use either t2.h_id or t2.id.

how to select one row in joining multiple tables based on a case statement

cross/outer apply (
    select top 1 status
    from B b
    where b.id = a.id
    order by case when status in ('OA', 'OB') then 1 else 2 end, status
)

or

inner/left join (
    select
        id,
        case min(case when status in ('OA', 'OB') then 1 else 2 end)
            when 1 then min(cast when status in ('OA', 'OB') then status end)
            when 2 then min(status)
        end
    from B
    group by id
) b on b.id = a.id

Compare rows from two join tables and gives result from two tables in alternate fashion

I take it you want to compare all pairs of rows between two tables. Just realize this is going to get big and slow very quickly.

with X as (
    select *, row_number() over (order by "First Name", "Last Name", "Job") as rnX
    from TableX
), Y as (
    select *, row_number() over (order by "First Name", "Last Name", "Job") as rnY
    from TableY
), horizontal as (
    select
        rnX, rnY,
        case when x."First Name" = y."First Name" then x."First Name"
            else '>> ' + x."First Name" + ' <<' end as FirstNameX,
        case when x."First Name" = y."First Name" then y."First Name"
            else '>> ' + y."First Name" + ' <<' end as FirstNameY,
        case when x."Last Name" = y."Last Name" then x."Last Name"
            else '>> ' + x."Last Name" + ' <<' end as LastNameX,
        case when x."Last Name" = y."Last Name" then y."Last Name"
            else '>> ' + y."Last Name" + ' <<' end as LastNameY,
        case when x."Job" = y."Job" then x."Job"
            else '>> ' + x."Job" + ' <<' end as JobX,
        case when x."Job" = y."Job" then y."Job"
            else '>> ' + y."Job" + ' <<' end as JobY
    from X as x cross join Y as y
), interleave as (
    select
        rnX, rnY, n,
        case when n = 1 then 'X' else 'Y' end as src,
        case when n = 1 then rnX else rnY end as rn,
        case when n = 1 then FirstNameX else FirstNameY end as "First Name",
        case when n = 1 then LastNameX else LastNameY end as "Last Name",
        case when n = 1 then JobX else JobY end as "Job"
    from horizontal cross apply (select n from (values (1), (2)) d(n)) c
)
select src, rn, "First Name", "Last Name", "Job" from interleave
order by rnX, rnY, n;

http://rextester.com/JRQ91122

MySQL return first row of a joined table

SELECT c.*, d.*
FROM country c 
  INNER JOIN ducks d 
    ON d.id =                         --- guessing the ducks Primary Key here
       ( SELECT dd.id                 --- and here  
         FROM ducks dd
         WHERE c.id = dd.country_id
         ORDER BY dd.rating DESC
         LIMIT 1
       )

An index on (country_id, rating, id) for MyISAM table or (country_id, rating) for InnoDB table, would help.

This query will show only one duck per country, even with more than one having the same rating. If you want ducks with tied rating to appear, use @imm's GROUP BY answer.

---

Related Topics