MYSQL: select the top N candidates

I have the following DB scheme for university elections:

for each department, I have the following positions:

1 CHEF (which is the candidate_position = 1)

& 6 Members (which is the candidate_position = 2)

I want to obtain the winners of the election in each department.

to obtain the winner of CHEF position in "Informatique" department, I did the following query:

SELECT doctor.firstname, doctor.lastname, votes

FROM (SELECT COUNT(*) AS votes FROM candidate_votes WHERE candidate_votes.candidate_position = 1 GROUP BY candidate_votes.candidate_id) AS votes, doctor 

INNER JOIN department_candidates ON department_candidates.doctor_id = doctor.id 

INNER JOIN department ON department.id = department_candidates.department_id AND department.name = 'Informatique' 

INNER JOIN candidate_votes ON candidate_votes.candidate_id = doctor.id AND candidate_votes.candidate_position = 1 

GROUP BY candidates_votes.candidate_id

please note I didn't use LIMIT 1 because may be there is a tie (or draw) of votes between multiple candidates

based on the results, I think that my query of selecting the winner of Chef position is right, But I want some help to know how to select the first 6 candidates of Member position ?

Data set:

--
-- Table structure for table `candidate_votes`
--

DROP TABLE IF EXISTS `candidate_votes`;
CREATE TABLE IF NOT EXISTS `candidate_votes` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `candidate_id` int(11) NOT NULL,
  `voter_id` int(11) NOT NULL,
  `candidate_position` tinyint(1) NOT NULL COMMENT '1: chef, 2: member',
  `date` date NOT NULL,
  PRIMARY KEY (`id`),
  KEY `fk-candidate_votes-voter_id` (`voter_id`),
  KEY `fk-candidate_votes-candidate_id_idx` (`candidate_id`)
) ENGINE=InnoDB AUTO_INCREMENT=36 DEFAULT CHARSET=utf8;

--
-- Dumping data for table `candidate_votes`
--

INSERT INTO `candidate_votes` (`id`, `candidate_id`, `voter_id`, `candidate_position`, `date`) VALUES
(24, 2, 1, 1, '2018-05-26'),
(25, 1, 1, 2, '2018-05-26'),
(26, 6, 1, 2, '2018-05-26'),
(27, 5, 1, 2, '2018-05-26'),
(28, 7, 1, 2, '2018-05-26'),
(29, 8, 1, 2, '2018-05-26'),
(30, 9, 1, 2, '2018-05-26'),
(31, 2, 2, 1, '2018-05-16'),
(32, 3, 7, 1, '2018-05-22'),
(33, 3, 8, 1, '2018-05-22'),
(34, 4, 6, 2, '2018-05-29'),
(35, 7, 6, 2, '2018-05-29');

-- --------------------------------------------------------

--
-- Table structure for table `department`
--

DROP TABLE IF EXISTS `department`;
CREATE TABLE IF NOT EXISTS `department` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `department-name` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;

--
-- Dumping data for table `department`
--

INSERT INTO `department` (`id`, `name`) VALUES
(1, 'Informatique'),
(2, 'Mathematique'),
(4, 'physique');

-- --------------------------------------------------------

--
-- Table structure for table `department_candidates`
--

DROP TABLE IF EXISTS `department_candidates`;
CREATE TABLE IF NOT EXISTS `department_candidates` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `department_id` int(11) NOT NULL,
  `doctor_id` int(11) NOT NULL,
  `candidate_position` tinyint(1) NOT NULL COMMENT '1: chef, 2: member',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=15 DEFAULT CHARSET=utf8;

--
-- Dumping data for table `department_candidates`
--

INSERT INTO `department_candidates` (`id`, `department_id`, `doctor_id`, `candidate_position`) VALUES
(5, 1, 3, 1),
(7, 1, 4, 2),
(8, 1, 1, 2),
(9, 1, 2, 1),
(10, 1, 6, 2),
(11, 1, 5, 2),
(12, 1, 7, 2),
(13, 1, 8, 2),
(14, 1, 9, 2);

-- --------------------------------------------------------

--
-- Table structure for table `doctor`
--

DROP TABLE IF EXISTS `doctor`;
CREATE TABLE IF NOT EXISTS `doctor` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `firstname` varchar(255) NOT NULL,
  `lastname` varchar(255) NOT NULL,
  `department_id` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8;

--
-- Dumping data for table `doctor`
--

INSERT INTO `doctor` (`id`, `firstname`, `lastname`, `department_id`) VALUES
(1, 'doc1_fn', 'doc1_ln', 1),
(2, 'doc2_fn', 'doc2_ln', 1),
(3, 'doc3_fn', 'doc3_ln', 1),
(4, 'doc4_fn', 'doc4_ln', 1),
(5, 'doc5_fn', 'doc5_ln', 1),
(6, 'doc6_fn', 'doc6_ln', 1),
(7, 'doc7_fn', 'doc7_ln', 1),
(8, 'doc8_fn', 'doc8_ln', 1),
(9, 'doc9_fn', 'doc9_ln', 1);

-- --------------------------------------------------------

Sqlfiddle DEMO

Solution

Consider the following:

SELECT x.*
     , CASE WHEN @prev_position = candidate_position THEN CASE WHEN @prev_total = total THEN @i:=@i ELSE @i:=@i+1 END ELSE @i:=1 END i
     , @prev_position := candidate_position prev_position
     , @prev_total := total prev_total
  FROM
     (
SELECT candidate_id
     , candidate_position
     , COUNT(*) total 
  FROM candidate_votes  
 GROUP 
    BY candidate_id
     , candidate_position
     ) x
  JOIN 
     ( SELECT @prev_position := null,@prev_total:=null,@i:=0) vars
 ORDER
    BY candidate_position
     , total DESC;

+--------------+--------------------+-------+------+---------------+------------+
| candidate_id | candidate_position | total | i    | prev_position | prev_total |
+--------------+--------------------+-------+------+---------------+------------+
|            2 |                  1 |     2 |    1 |             1 |          2 |
|            3 |                  1 |     2 |    1 |             1 |          2 |

|            7 |                  2 |     2 |    1 |             2 |          2 |

|            8 |                  2 |     1 |    2 |             2 |          1 |
|            9 |                  2 |     1 |    2 |             2 |          1 |
|            1 |                  2 |     1 |    2 |             2 |          1 |
|            4 |                  2 |     1 |    2 |             2 |          1 |
|            5 |                  2 |     1 |    2 |             2 |          1 |
|            6 |                  2 |     1 |    2 |             2 |          1 |
+--------------+--------------------+-------+------+---------------+------------+

In this example, i represents rank. For position 1, we can see that two candidates tied for first place. For position 2, there was one outright winner, with all remaining candidates tying for second place.