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.

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 Optimize UNION query

I’m trying to optimize a query. My question seems to be similar to MySQL, Union ALL and LIMIT and the answer might be the same (I’m afraid). However in my case there’s a stricter limit (1) as well as

MySQL, Union ALL and LIMIT

Let’s say I want to perform this query: (SELECT a FROM t1 WHERE a=10 AND B=1) UNION ALL (SELECT a FROM t2 WHERE a=11 AND B=2) UNION ALL (SELECT a FROM t3 WHERE a=12 AND B=3) ORDER BY a LIMIT 1000; I

mysql union limit problem

I want a paging script working properly basically but the situation is a bit complex. I need to pick data from union of two sql queries. See the query below. I have a table book and a table bookvisit.

MySQL query with 2 SELECTs, UNION and LIMIT – how to count all items?

I can’t figure out how to count results from my query because I limit the output: (SELECT new as type, name FROM newUsers WHERE name LIKE ‘%John%’) UNION (SELECT old as type, name FROM oldUsers WH

Count mysql union type query

I have a union type query. How can I count the number of rows it queries? mysql_query( (SELECT 1 as sort_col,performerid,pic0 FROM $table WHERE performerid IS NOT NULL $performeridSql) UNION (SELECT

MySQL – Using UNION with LIMIT

i noticed that (SELECT title, relavency, ‘search1’ as source FROM search1 ORDER BY relavency DESC LIMIT 10) UNION (SELECT title, relavency, ‘search2’ as source FROM search2 ORDER BY relavency DESC LI

Exclude results from a MySQL UNION query

What is the proper way to exclude results from a MySQL UNION? I’m looking for the equivalent to: (query1) UNION (query2) UNION (query3) EXCEPT (query4)

Get result row count for individual SELECT statements with UNION ALL in MySQL when using LIMIT

I need to get the row count for individual SELECT statements in an UNION ALL SELECT query that uses LIMIT. The MySQL docs are pretty clear about getting the global row count for the query (place indiv

Union as sub query MySQL

I’m wanting to optimize a query using a union as a sub query. Im not really sure how to construct the query though. I’m using MYSQL 5 Here is the original query: SELECT Parts.id FROM Parts_Category, P

Slow UNION Query – MySQL

I have a UNION query consisting of two fast queries. ( SELECT DISTINCT ( SELECT strStatus FROM User_User_XR uuxr WHERE ( uuxr.intUserId1 = ‘1’ AND uuxr.intUserId2 = u.intUserId ) ) AS strFriendStatus1

Answers

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