Only want 3 records with the same jobid then skip to the next jobs

Question

I need to select 8 people from a movie crew. I only want to have 3 producers and then let the query continue with other jobs.

This is my query now:

SELECT EmployeeID, j.NameEN, e.Firstname, e.Lastname, j.ID AS JobId 
  FROM crew AS c
  LEFT JOIN job AS j ON c.JobID = j.ID
  LEFT JOIN employee AS e ON c.EmployeeID = e.Id
  WHERE c.MovieID = 237038
  ORDER BY j.SortOrder ASC, c.JobID ASC, e.Score DESC
  LIMIT 8

How can I achieve selecting only 3 employee's with the same job id?

Sample output

EmployeeID NameEN       Firstname     Lastname   JobId 
802        Director     Chris         Columbus   2
5707       Director     David         Yates      2
1705       Director     Mike          Newell     2
175        Director     Alfonso       Cuarón     2
5115       Writer       Steve         Kloves     3
2041       Writer       Michael       Goldenberg 3
11096      Writer       J.K.          Rowling    3

You can see here that I have 4 Directors. The desired output would be that there were 3 Directors max.

Most DBMSes support `row_number() over (partition by JobId order by ...)` to determine the 1st three per job. — dnoeth, Sep 28 '18 at 07:55
How do you know, which Directors you need to get from four of them (get the three) — D-Shih, Sep 28 '18 at 07:59
@D-Shih Because of the ordering on their score. I just need to skip getting the last one so there is a space left for another employee with a different jobId that doesn't have a maxcount of 3. — Petah, Sep 28 '18 at 08:02
A result set without a data set is like a stick without a lollipop — Strawberry, Sep 28 '18 at 08:44
Possible duplicate of [Get top n records for each group of grouped results](https://stackoverflow.com/questions/12113699/get-top-n-records-for-each-group-of-grouped-results) — Ankit Bajpai, Sep 28 '18 at 09:06
You can [emulate row_number() functionality](http://www.mysqltutorial.org/mysql-row_number/) in older versions of MySQL — Madhur Bhaiya, Sep 28 '18 at 09:36

score 1 · Answer 1 · answered Sep 28 '18 at 10:50

I think the simplest method for your particular problem is union all:

(SELECT EmployeeID, j.NameEN, e.Firstname, e.Lastname, j.ID AS JobId 
 FROM crew c LFT JOIN
      job j
      ON c.JobID = j.ID LEFT JOIN
      employee e
      ON c.EmployeeID = e.Id
 WHERE c.MovieID = 237038 AND
       j.NameEN = 'Producer'
 ORDER BY j.SortOrder ASC, c.JobID ASC, e.Score DESC
 LIMIT 3
) UNION ALL
(SELECT EmployeeID, j.NameEN, e.Firstname, e.Lastname, j.ID AS JobId 
 FROM crew c LFT JOIN
      job j
      ON c.JobID = j.ID LEFT JOIN
      employee e
      ON c.EmployeeID = e.Id
 WHERE c.MovieID = 237038 AND
       j.NameEN <> 'Producer'
 ORDER BY j.SortOrder ASC, c.JobID ASC, e.Score DESC
 LIMIT 5
);

Only want 3 records with the same jobid then skip to the next jobs

1 Answers1