skip to Main Content

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.

2

Answers


  1. 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.
  2. 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.
Please signup or login to give your own answer.
Back To Top
Search