103

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.

random
  • 9,774
  • 10
  • 66
  • 83

3 Answers3

258

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);
Alex Martelli
  • 854,459
  • 170
  • 1,222
  • 1,395
  • I was going by the MySQL documentation that said the ORDER BY came after the UNION.....and forgot about the parentheses! Thanks –  Sep 13 '09 at 20:38
  • @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...)... – Alex Martelli Sep 14 '09 at 00:32
  • 16
    This works in MySql, but it doesn't look like SQLite supports this in case anyone runs into this question on google – Joe Lyga Sep 20 '12 at 15:32
  • 4
    It will return max 20 records. And only if those are unique rows. U have to use UNION ALL on non-unique rows. – hugo24 Apr 29 '14 at 08:10
  • Prefect, just what I needed to get the fields I needed with limits on my two queries – carnini Jun 09 '21 at 14:41
  • 1
    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. – noox Dec 30 '22 at 14:37
6

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);
Bink
  • 1,934
  • 1
  • 25
  • 41
bioye
  • 63
  • 1
  • 5
  • 2
    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); ``` – Pj Toopmuch Mar 05 '21 at 18:16
  • @PjToopmuch Yep. I'm going to try to edit the answer. – Bink May 04 '23 at 20:28
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

Arpan Saini
  • 4,623
  • 1
  • 42
  • 50