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:

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.

CodePudding user response：

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; 

CodePudding user response：

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).