Limiting an SQL Join

LIMITing an SQL JOIN

So assuming we can exclude the user table, it could be rewritten as:

select * from expense, transaction where expense_id = transaction_expense_id

Now if you want to apply a limit, you could do it like this:

select * from expense, transaction where expense_id = transaction_expense_id and 
  expense_id in (select expense_id from expense limit 1)

Would that do what you wanted? Obviously you need to be cautious about what order your expense_ids are going to come back in, so you probably want to use ORDER BY whatever.

Edit: Given the MySQL limitation described in your comment below, maybe this will work:

select * from (select id from expense order by WHATEVER limit 1) as t1, transaction where expense_id=transaction_expense_id;

Ben

Inner join limit the rows from second table

Select the ten transactions in a subquery:

select *
from
(
  select *
  from transactions
  order by time desc
  limit 10
) t
join queries q on q.id like concat(t.tid, '%')
order by t.time desc, t.tid, q.timestamp desc;

Limit result of inner join to 1

Since no logic has been provided for determining which row is the 'first record' (see @HoneyBadger's comments), and you've commented that ordering is not important for your business case, we'll use max() to extract a single row per unique orderid:

select  o.id        as oId,
        op.id       as opId,
        op.orderid,
        op.descr

from    ord      o,
        orderpos op,

        (select op2.orderid,
                max(op2.descr) as descr
        from    orderpos op2
        group by op2.orderid
        ) dt

where   op.orderid = o.id
and     op.orderid = dt.orderid
and     op.descr   = dt.descr
order by 1,2
go

 oId     opId    orderid  descr
 ------- ------- -------- ----------
       1       1        1 huba
       2       3        2 foo

Your join criteria between ord(o) and orderpos(op) remains the same; the addition of the derived table (dt) allows us to further limit the rows that are of interest from orderpos(op).

In this case it's just a coincidence that our use of max() generated the output you're looking for. [HINT: replace max() with min() to display '2/blub' instead of '1/huba'.]

---

Same idea but using a correlated sub-query instead of a derived table:

select  o.id        as oId,
        op.id       as opId,
        op.orderid,
        op.descr

from    ord      o,
        orderpos op

where   op.orderid = o.id
and     op.descr = (select      max(op2.descr)
                        from    orderpos op2
                        where   op2.orderid = op.orderid)
order by 1,2
go

 oId     opId    orderid  descr
 ------- ------- -------- ----------
       1       1        1 huba
       2       3        2 foo

---

Alternatively we could have replaced max(op2.descr) with max(op2.id).

The key issue being to pick some method ... any method in this case ... that allows us to pick a single row from op2 for a given orderid (via the group by op2.orderid).

NOTE: The proposed solutions work as long a given descr value is unique for a given orderid (eg, you can't have 2x rows in orderpos with the same orderid and descr). If this is an invalid assumption then we'll need more sample data and/or a better description of the data in the orderpos table (eg, pk contraint, unique index, etc).

LIMIT / Filtering on LEFT JOIN

Snowflake supports joining laterally. That is, on to a function or correlated sub-query. This allows you to join on to a query that returns just one row (per input row).

SELECT  
  purchases.date
 ,purchases.revenue
 ,clicks.campaign_id
FROM 
  purchases    
LEFT JOIN LATERAL
(
  SELECT
    campaign_id 
   ,user_id
   ,click_time                       
  FROM 
    campaign_clicks
  WHERE
            user_id = purchases.user_id
    -- only select campaign clicks that occurred before the purchase                
    AND click_time <  purchases.purchase_time
    -- only include clicks that occurred within 3 days of the purchase               
    AND click_time >= DATEADD(days, -3, purchases.purchase_time)
  ORDER BY
    click_time DESC
  LIMIT
    1                        
)
  AS clicks

How to limit SQL query with JOIN

By MySQL version 10.5 I assume you mean MariaDB version 10.5... seeing as MySQL is only on version 8.0 at the moment ;)

I'm not too familiar with the syntax differences between MySQL and MariaDB, but here's a query that works in MySQL 8.0... which technically should work for you in MariaDB 10.5 (seeing as they've had window functions since 10.2 - https://mariadb.com/kb/en/window-functions-overview/)

SELECT
    r.*
FROM
(
    SELECT
    n.id,
    n.title,
    n.main_image,
    n.services,
    i.file_name,
    ROW_NUMBER() OVER (PARTITION BY i.new_id ORDER BY i.id) AS row_num
    FROM news n
    INNER JOIN images i ON i.new_id = n.id
) r
WHERE r.row_num <= 2;

Hope this helps :)

Setting a LIMIT before JOINs of tables

You can use JOIN + Subquery

SELECT cp.id_sale, fp.id_file 
   FROM (SELECT id, id_sale FROM client_purchases ORDER BY id LIMIT 25) AS cp 
JOIN (SELECT id FROM file_purchases ORDER BY id LIMIT 25) AS fp 
    ON cp.id_sale = fp.id_sale 

However this may speed up your query or it may make it go even slower. It all depends on what kinds of indexes you have and how many records you have in the table.

What seems fast with 100 records might be slow with 100M records and vice verce.

Add LIMIT to INNER JOIN (SQL Server)

You can just use TOP:

SELECT TOP 100 [ToReadType_60]
FROM [dbo].['SMART Month End'] e INNER JOIN
     [dbo].['SMART Reads$'] p 
     ON e.ContractNumber = p.[Contract Number];

I would also suggest using = instead of like. And indexes on the columns used for the joins can help performance.

Implementing LIMIT against rows of single table while using LEFT JOIN

A query like:

SELECT * FROM items ORDER BY ? LIMIT 10

will return 10 rows (at most) from items.

You need to provide the column(s) for the ORDER BY clause if you have some specific sorting condition in mind.

If not then remove the ORDER BY clause, but in this case nothing guarantees the resultset that you will get.

So all you have to do is LEFT join the above query to images:

SELECT it.*, im.*
FROM (SELECT * FROM items ORDER BY ? LIMIT 10) it
LEFT JOIN images im
ON im.item_id = it.id

---

Related Topics