Jon Abaca Jon Abaca - 5 months ago 19
SQL Question

Mins of Max SQL

Semesters Table
+----+------+
| ID | Name |
+----+------+
| 1 | 1st |
| 2 | 2nd |
+----+------+

Subjects Table
+----+-------------+-------------+
| ID | Semester Id | Name |
+----+-------------+-------------+
| 1 | 1 | Mathematics |
| 2 | 1 | English |
| 3 | 2 | Mathematics |
| 4 | 2 | English |
+----+-------------+-------------+

Tests Table
+----+------------+-------+
| ID | Subject ID | Score |
+----+------------+-------+
| 1 | 1 | 70 |
| 2 | 1 | 75 |
| 3 | 2 | 75 |
| 4 | 2 | 70 |
| 5 | 3 | 75 |
| 6 | 3 | 70 |
| 7 | 4 | 70 |
| 8 | 4 | 75 |
+----+------------+-------+


I can get the scores of the 2nd test by using MAX on the ID of the tests, and then grouping them by the subject id. However, then I have to get the minimums of the scores grouped by the semester.

Is it possible to get the lowest scoring 2ND test of each semester in a single SQL statement?

The result set would look like this.

+----------+-------------+-------+
| Semester | Subject | Score |
+----------+-------------+-------+
| 1st | English | 70 |
| 2nd | Mathematics | 70 |
+----------+-------------+-------+


This is in MySQL.

Answer Source

You are looking for the second lowest tests per semester.

Build row numbers for the ordered tests per semester and stay with those numbered #2. One way to do this is a correlated subquery. Another would be variables.

select 
  sem.name as semester,
  sub.name as subject, 
  tst.score
from semesters sem 
join subjects sub on sub.semester_id = sem.id
join tests tst on tst.subject_id = sub.id
where
(
  select count(*)
  from subjects sub2
  join tests tst2 on tst2.subject_id = sub2.id
  where sub2.semester_id = sub.semester_id
    and sub2.id <= sub.id
    and tst2.score <= tst.score
) = 2
order by sub.semester_id;

In case of ties one of the rows is picked, just as shown in your example.

Working with variables is probably faster than above query. You will easily find the method by looking for how to emulate ROW_NUMBER in MySQL. (Other DBMS use ROW_NUMBER which is much simpler, but MySQL doesn't feature this function.)