116

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.

Improve this question

3 Answers 3

Reset to default
284

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);
Improve this answer
Sign up to request clarification or add additional context in comments.

6 Comments

I was going by the MySQL documentation that said the ORDER BY came after the UNION.....and forgot about the parentheses! Thanks
@Mauro, che ne dici di accettare la risposta, se ti risolve il problema, eh?!-) Usa il segno di "checkmark" sotto il numero di upvotes (attualmente tre...)...
This works in MySql, but it doesn't look like SQLite supports this in case anyone runs into this question on google
It will return max 20 records. And only if those are unique rows. U have to use UNION ALL on non-unique rows.
If you know that the results are unique or you simply don't care if there are duplicates you should use UNION ALL. UNION removes duplicate rows. With UNION duplicates are removed which has - depending on the number of rows - a performance impact.
|
7

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 * FROM 
(SELECT a FROM t2 WHERE a=11 AND B=2 ORDER BY a LIMIT 10);
Improve this answer

2 Comments

answer needed a slight modification for me on SQLite. sql SELECT * FROM (SELECT a FROM t1 WHERE a=10 AND B=1 ORDER BY a LIMIT 10) UNION SELECT * FROM (SELECT a FROM t2 WHERE a=11 AND B=2 ORDER BY a LIMIT 10);
@PjToopmuch Yep. I'm going to try to edit the answer.
0

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

Improve this answer

1 Comment

we can use either "" or instead of "" you can use , "user_defined_name1.*". Both approach works

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.