Combining UNION and LIMIT operations in MySQL query

MysqlSqlUnionLimit

Mysql Problem Overview


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.

Mysql Solutions


Solution 1 - Mysql

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 - Mysql

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);

Solution 3 - Mysql

In Teradata we can't use union with top queries as it, if you do you get an error, which needs to be tweaked as shown below. Adding solution for Teradata users.

Union and Top can written together as given below in Teradata

Attributions

All content for this solution is sourced from the original question on Stackoverflow.

The content on this page is licensed under the Attribution-ShareAlike 4.0 International (CC BY-SA 4.0) license.

Content TypeOriginal AuthorOriginal Content on Stackoverflow
Questionuser172517View Question on Stackoverflow
Solution 1 - MysqlAlex MartelliView Answer on Stackoverflow
Solution 2 - MysqlbioyeView Answer on Stackoverflow
Solution 3 - MysqlArpan SainiView Answer on Stackoverflow