Combining UNION and LIMIT operations in MySQL query

Question

I have a Jobs and a Companies table, and I want to extract 20 jobs that meet the following criteria:

1. Jobs only from two (2) named companies
2. There can at most be 10 jobs per company

I have tried the following SELECT with UNION DISTINCT, but the problem is that the LIMIT 0,10 applies to the whole result set. I want it to apply to each of the companies.

If there aren't 10 jobs per company, then the query should return all the jobs it finds.

SELECT c.name, j.title, j.`desc`, j.link 
  FROM jobs_job j
INNER JOIN companies_company c ON j.company_id = c.id
WHERE c.name IN ('Company1')
UNION DISTINCT
SELECT c.name, j.title, j.`desc`, j.link 
  FROM jobs_job j
INNER JOIN companies_company c ON j.company_id = c.id
WHERE c.name IN ('Company2')
ORDER by name, title
LIMIT 0,10

I am new to MySQL, so realise there may be a smarter way to do this instead of with UNION, so any suggestions for improvements are definitely welcome.

Solution 1

Quoting the docs,

To apply ORDER BY or LIMIT to an individual SELECT, place the clause inside the parentheses that enclose the SELECT:

(SELECT a FROM t1 WHERE a=10 AND B=1 ORDER BY a LIMIT 10)
UNION
(SELECT a FROM t2 WHERE a=11 AND B=2 ORDER BY a LIMIT 10);

Solution 2

Improving on Alex's answer and based on Joe's observation, the following should work in SQLite:

SELECT * FROM 
(SELECT a FROM t1 WHERE a=10 AND B=1 ORDER BY a LIMIT 10)
UNION
(SELECT a FROM t2 WHERE a=11 AND B=2 ORDER BY a LIMIT 10);

Comments

[Admin]

I was going by the MySQL documentation that said the ORDER BY came after the UNION.....and forgot about the parentheses! Thanks

[Alex Martelli]

@Mauro, che ne dici di accettare la risposta, se ti risolve il problema, eh?!-) Usa il segno di "checkmark" sotto il numero di upvotes (attualmente tre...)...

[Joe Lyga]

This works in MySql, but it doesn't look like SQLite supports this in case anyone runs into this question on google

[hugo24]

It will return max 20 records. And only if those are unique rows. U have to use UNION ALL on non-unique rows.

[Pj Toopmuch]

answer needed a slight modification for me on SQLite. sql SELECT * FROM (SELECT a FROM t1 WHERE a=10 AND B=1 ORDER BY a LIMIT 10) UNION SELECT * FROM (SELECT a FROM t2 WHERE a=11 AND B=2 ORDER BY a LIMIT 10);

[carnini]

Prefect, just what I needed to get the fields I needed with limits on my two queries

[Arpan Saini]

we can use either "" or instead of "" you can use , "user_defined_name1.*". Both approach works