How to Retrieve the Top N Jobs from Multiple Companies Using MySQL's UNION and LIMIT?

MySQL's UNION and LIMIT: Efficiently Retrieving Top Jobs from Multiple Companies

This article demonstrates how to retrieve a specified number of top jobs from different companies using MySQL's UNION and LIMIT clauses. The challenge lies in applying a LIMIT to each company's job selection independently, rather than globally across all companies.

A common, but flawed, approach uses UNION DISTINCT with a single LIMIT clause. This applies the limit to the combined result set, not to each company individually.

The solution, as detailed in the MySQL documentation, involves applying the LIMIT clause within each individual SELECT statement of the UNION query. This ensures independent limiting for each company's data.

Here's the optimized query to retrieve the top 10 jobs from 'Company1' and 'Company2':

<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)</code>

This query effectively retrieves the top 10 jobs from each company, satisfying the requirement for independent limiting within the UNION operation. This technique is crucial for accurate and efficient data retrieval when working with multiple data sources.

The above is the detailed content of How to Retrieve the Top N Jobs from Multiple Companies Using MySQL's UNION and LIMIT?. For more information, please follow other related articles on the PHP Chinese website!