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