How to Limit Results Per Company When Using UNION in MySQL?

Combining UNION and LIMIT operations in MySQL limits the number of results per company

The goal is to retrieve 20 jobs matching specific criteria from two specified companies, while limiting the number of results to a maximum of 10 per company. Problems are encountered when using UNION DISTINCT and LIMIT because the latter affects the entire result set rather than individual result sets for each company.

The following solution resolves this issue:

<code class="language-sql">((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')
ORDER by name, title
LIMIT 0, 10)
UNION
(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))
ORDER BY name;</code>

The key tweak is to nest the LIMIT clause within individual SELECT statements rather than applying it to the combined result set. This ensures that results are limited to a maximum of 10 per company. We then use another outer ORDER BY statement to sort the final combined result set by company name.

Remember that MySQL applies ORDER BY after UNION, so another ORDER BY is needed to sort the combined result set.

The above is the detailed content of How to Limit Results Per Company When Using UNION in MySQL?. For more information, please follow other related articles on the PHP Chinese website!