CODEWITHSUNDEEP
sqlsqlite
user1934887
user1934887

Reputation: 297

Limiting results in SQLite

I have two tables, customers (with columns A, B) and orders (with columns C,D,A; the last one a foreign key).

This query returns the data I'm interested in:

SELECT customers.A, customers.B, orders.C, orders.D 
FROM customers, orders
WHERE customers.A = orders.A AND customers.B < 5 AND orders.D < 5

If I add LIMIT 10, I will get first ten 10 results (basically 10 orders), but what I want to do is to limit output based on the number of unique customers. So there would be exactly 10 unique customers in the result and therefore at least 10 - but likely more - orders. I think it's possible to do that with a subquery but I can't figure it out.

Upvotes: 1

Views: 140

Answers (2)

Leonidos
Leonidos

Reputation: 10518

SELECT customers.A, customers.B, orders.C, orders.D 
FROM customers, orders
WHERE customers.A IN (
    SELECT DISTINCT customers.A 
    FROM customers, orders
    WHERE customers.A = orders.A AND 
          customers.B < 5 AND 
          orders.D < 5
    LIMIT 10
) AND customers.A = orders.A AND orders.D < 5

Subquery select 10 distinct customers who have B < 5 and at least one order with D < 5. Main query selects all customers who were selected in subquery (I supposed A is ID) and their's orders with D < 5 (we do not need to check B again)

Upvotes: 2

Anton Kovalenko
Anton Kovalenko

Reputation: 21507

Something like this:

SELECT c.A, c.B, orders.C, orders.D 
FROM (SELECT * FROM customers WHERE customers.B < 5 
      ORDER BY ....some-criteria... 
      LIMIT 10) c,
     orders
WHERE c.A = orders.A AND orders.D < 5

(Verified that sqlite successfully parses LIMIT in subqueries. Hopefully it doesn't ignore this LIMIT silently).

Upvotes: 2

Related Questions