How to use MySQL to output a query with names of all top 5 students from 3 different tables (also sort it in desc order by name)?

Question

original question:

Your University has three courses. Information about the students in these courses is stored in the mysql_students, postgresql_students and java_students tables.

The tables are already created with the following schema and required data :

id          INT Primary key
name        VARCHAR(100)
score       INT

Write an SQL statement to find the names of top 5 students in each course. Resulting table should have one column names. The names of the student from each course should be in order by mysql_students, postgresql_students and java_students. The names of student who has same course should be sorted in ascending order.

Given data:

CREATE TABLE mysql_students(
    id INT Primary key,
    name VARCHAR(100),
    score INT
);
CREATE TABLE postgresql_students(
    id INT Primary key,
    name VARCHAR(100),
    score INT
);
CREATE TABLE java_students (
    id INT Primary key,
    name VARCHAR(100),
    score INT
);

INSERT INTO  mysql_students VALUES(1,'Maria Anders',750);
INSERT INTO  mysql_students VALUES(2,'Ana Trujillo',890);
INSERT INTO  mysql_students VALUES(3,'Antonio Moreno',400);
INSERT INTO  mysql_students VALUES(4,'Thomas Hardy',910);
INSERT INTO  mysql_students VALUES(5,'Christina',600);
INSERT INTO  mysql_students VALUES(6,'Hanna',120);
INSERT INTO  mysql_students VALUES(7,'Frederique',891);
INSERT INTO  mysql_students VALUES(8,'Martin Sommer',490);
INSERT INTO  mysql_students VALUES(9,'Laurence',790);
INSERT INTO  mysql_students VALUES(10,'Elizabeth',690);
                                   
INSERT INTO  postgresql_students VALUES(1,'Victoria',750);
INSERT INTO  postgresql_students VALUES(2,'Patricio',800);
INSERT INTO  postgresql_students VALUES(3,'Francisco',400);
INSERT INTO  postgresql_students VALUES(4,'Yang',960);
INSERT INTO  postgresql_students VALUES(5,'Christina',675);
                                        
INSERT INTO  java_students VALUES(1,'Pedro',350);
INSERT INTO  java_students VALUES(2,'Elizabeth',490);
INSERT INTO  java_students VALUES(3,'Francisco',400);
INSERT INTO  java_students VALUES(4,'Sven',510);
INSERT INTO  java_students VALUES(5,'Janine',600);                                       
INSERT INTO  java_students VALUES(6,'Hanna',120);
INSERT INTO  java_students VALUES(7,'Frederique',891);

The expected output:

names
Ana Trujillo
Frederique
Laurence
Maria Anders
Thomas Hardy
Christina
Francisco
Patricio
Victoria
Yang
Elizabeth
Francisco
Frederique
Janine
Sven

Please keep in mind I am a beginner. Now, I tried using union and it almost works, the issue being that it sorts the scores-- but doesn't sort the names alphabetically w.r.t their course after it fetches the records from the tables, and I am not sure how to do it. This is what I have so far:

db<>fiddle here

SELECT *
FROM (
        (SELECT name 'names'
         FROM mysql_students
         ORDER BY score DESC
         LIMIT 5)
      UNION
        (SELECT name 'names'
         FROM postgresql_students
         ORDER BY score DESC
         LIMIT 5)
      UNION
        (SELECT name 'names'
         FROM java_students
         ORDER BY score DESC
         LIMIT 5))
ORDER BY NAMES

Actual Results:

names
Ana Trujillo
Christina
Elizabeth
Francisco
Frederique
Janine
Laurence
Maria Anders
Patricio
Sven
Thomas Hardy
Victoria
Yang

It is very likely that my approach itself is wrong-- I have been brainstorming for hours and I cannot think of how to do this. Please help. Guidance is greatly appreciated.

Can you show the result of your query? My brain is fizzing trying to imagine what you got and how it differs from the desired result. Ultimately is seems like you are on a good track, but I can't see what the actual problem is. Edit to add, I think there's no requirement to sort by score? Maybe you're just driving yourself crazy with a requirement that's not part of the original question. — Jerry, Apr 17 '22 at 04:17
@ioshiii - I added the results of your attempted query above. Please update if needed. — SOS, Apr 17 '22 at 05:55
*"...but doesn't sort the names alphabetically w.r.t their course after it fetches the records from the tables..."* Correct, because the current query says order everything alphabetically by name, i.e. `order by names`. To sort the results first by "course" and *then* name, the each of the 3 UNION queries need an extra column to indicate the associated course for each student. You can use a constant number like 1, 2, 3. Then sort the results by that extra column first, and *then* name. Since this is a homework question, I'll leave the sql implementation to you. — SOS, Apr 17 '22 at 06:09
@SOS that's the results, yes. thank you. also , the sql implementation is _exactly_ what is hurting my brain! i don't even know what i'm doing wrong and the teacher refuses to elaborate. i think he wants us to use sql join or something. but he only taught us basics-- this feels unfairly advanced. — ioshiii, Apr 17 '22 at 14:59
But you do kinda know what's wrong, even if you don't know how to fix it yet :-). The query is sorting all the names *together*, instead of by each course and *then* name. So you need a way to indicate which course a student belongs to, something you can sort by. Ideally the schema would contain a course number, like mysql=1, postgres=2, java=3. Then you could just `order by courseNum, name`. But... since there isn't anything like that in the schema, you can create own, similar to this https://stackoverflow.com/a/3531301/8895292 . See how they use a constant 1,2,3 to create a psuedo column? — SOS, Apr 17 '22 at 16:54
I did it!!! Thanks, @SOS! I wish I saw your comment before getting lost in a googling black hole, though. — ioshiii, Apr 17 '22 at 18:09

score 1 · Accepted Answer · answered Apr 17 '22 at 18:08

I found a solution!!!

Select name as 'names'
from
( 
(Select name, 1 as filter from  mysql_students order by score desc limit 5)
Union all
(Select name, 2 as filter from  postgresql_students order by score desc limit 5)
union all
(Select name, 3 as filter from  java_students order by score desc limit 5)
)
as w
order by filter, names;

I looked up how to combine tables with UNION without changing the order of the records from individual tables. So, first I selected the top 5 students from each table, assigned the same number to each record per different table. Then I UNION'd them. This way, even if they get jumbled after UNION, they can be reordered in accordance to the table they belonged to using filter. So in the last line, the ORDER BY prioritizes filter, then name (now called names).

score 0 · Answer 2 · edited Apr 17 '22 at 17:38

0

in fact your code tell database to select the data and then sort all first student by name but the in assignment they want you to sort only the first five student by their name this is the mistake you can try use this code i think it work

select
  *
from 
(
  select
    *
  from 
    mysql_students
  order by score desc
  limit 5
) as t
order by name;

select
  * 
from 
(
  select
    * 
  from 
    postgresql_students
  order by score desc 
  limit 5
) as t 
order by name;

select
  * 
from 
(
  select
    * 
  from 
    java_students 
  order by score desc 
  limit 5
) as t
order by name;

edited Apr 17 '22 at 17:38

JoSSte

2,953
6
34
54

answered Apr 17 '22 at 11:21

Abdel Rahman Yosry

40
9

not quite.... there are many differences-- one mainly being the row name that appears after every query – ioshiii Apr 17 '22 at 15:11

How to use MySQL to output a query with names of all top 5 students from 3 different tables (also sort it in desc order by name)?

2 Answers2