I have this majors
table
id | name | group | score |
---|---|---|---|
1 | Computer Science | A | 688 |
2 | Electrical Engineering | B | 647 |
3 | Psychology | A | 616 |
(more data on the link below)
And this tests
table
id | group | score |
---|---|---|
1 | B | 690 |
2 | A | 650 |
3 | C | 600 |
Basically what I want to do is to make a list of the top 5 majors according to the score of the test based on the score of the major and the according group (except C
, C
is combination of A
and B
), limited to 5, and sorted descending. So the joined table will be
test_id | group | test_score | major_name | major_score |
---|---|---|---|---|
1 | B | 650 | Electrical Engineering | 647 |
2 | A | 690 | Computer Science | 688 |
2 | A | 690 | Pyschology | 616 |
3 | C | 690 | Computer Science | 688 |
3 | C | 650 | Electrical Engineering | 647 |
3 | C | 690 | Pyschology | 616 |
Is it possible to do this with JOIN
?
So far I have tried to use the basic JOIN
SELECT m.id, m.name, m.group, m.score
FROM majors AS m
JOIN tests AS t ON m.group = t.group
ORDER BY t.score DESC, m.score DESC
LIMIT 5;
But sure that is not the right query as it’ll limit all the results to 5, not limiting each of the joined data to 5.
2
Answers
Using MySQL8+, you would use ROW_NUMBER
Using MySQL 5.7:
see: DBFIDDLE
output:
you can use the ROW_NUMBER() window function to assign a rank to each row within each group, and then filter only the top 5 rows for each group. Here’s an example query: