CODEWITHSUNDEEP
mysqlsqljoingroup-by
Jon Abaca
Jon Abaca

Reputation: 229

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.

Upvotes: 1

Views: 74

Answers (2)

Thorsten Kettner
Thorsten Kettner

Reputation: 94913

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

Upvotes: 1

T Gray
T Gray

Reputation: 712

select a.name as semester
     , b.name as subject
     , min(c.score) as lowestscore
from subjects as b
   join semesters as a on a.id = b.semester_id
   join tests as c on c.subject_id = b.id
group by a.name, b.name

You will probably want to order by or something but this should give you what you are looking for. You could add more discriminators for range of semesters or subjects, but this will yield the name of the semester, the name of the subject and the minimum score for that semester/subject.

Upvotes: 0

Related Questions