Skip to content
Advertisement

Combining UNION and LIMIT operations in MySQL query

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.

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.

Advertisement

Answer

Quoting the docs,

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

User contributions licensed under: CC BY-SA
7 People found this is helpful
Advertisement