Mysql - Sort and limit joined table

DominiqueAltrez
January 6, 2024
231 views
0 votes
2 Answers

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

Link for the fiddle

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.

Tags: mysql sql

Answers

- Luuk
- January 6, 2024 at 3:20 pm
- 0 votes
0
Using MySQL8+, you would use ROW_NUMBER

Using MySQL 5.7:
```
SET @r:=0;
SELECT *
FROM (
SELECT m.id, m.name, m.group, m.score, @r:=@r+1 as row
FROM majors AS m
JOIN tests AS t ON m.group = t.group
ORDER BY t.score DESC, m.score DESC
-- LIMIT 5
) x
WHERE x.row <=5
ORDER BY x.score DESC
;
```
see: DBFIDDLE

output:

id name group score row

1 Computer Science A 688 1

100 Political Science B 671 3

101 Psychology A 651 4

102 Music B 578 5

10 Physics B 532 2
Login or Signup to reply.

- sahilkalra
- January 6, 2024 at 3:20 pm
- 0 votes
0
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:
```
SELECT
  t.id AS test_id,
  t.group AS test_group,
  t.score AS test_score,
  m.name AS major_name,
  m.score AS major_score
FROM
  tests AS t
  JOIN majors AS m ON t.group = m.group
WHERE
  (
    SELECT COUNT(*)
    FROM majors AS sub_m
    WHERE sub_m.group = t.group AND sub_m.score > m.score
  ) < 5
ORDER BY
  t.score DESC, m.score DESC;
```
Login or Signup to reply.