SQL Select rows with max value from joined table

Question

I have these 3 tables like that:

lecturers:

+-------------+---------+
| id-lecturer |  name   |
+-------------+---------+
| 1           | Johnson |
| 2           | Smith   |
| ...         | ...     |
| ...         | ...     |
+-------------+---------+

subjects:

+------------+---------+
| id-subject |  name   |
+------------+---------+
| 1          | Math    |
| 2          | Physics |
| ...        | ...     |
| ...        | ...     |
+------------+---------+

exams:

+---------+-------------+------------+------------+
| id-exam | id-lecturer | id-subject |    date    |
+---------+-------------+------------+------------+
| 1       | 5           | 1          | 1990-05-05 |
| 2       | 7           | 1          | ...        |
| 3       | 5           | 3          | ...        |
| ...     | ...         | ...        | ...        |
+---------+-------------+------------+------------+

When i try to do the first SELECT:

SELECT e.`id-lecturer`, e.`id-subject`, COUNT(e.`id-lecturer`) AS `exams-num`
FROM exams e    
JOIN subjects s ON e.`id-subject`=s.`id-subject`  
JOIN lecturers l ON e.`id-lecturer`=l.`id-lecturer`  
GROUP BY e.`id-lecturer`, e.`id-subject`

I get the right answer. It shows something like that:

+-------------+------------+-----------+
| id-lecturer | id-subject | exams-num |
+-------------+------------+-----------+
|        0001 |          1 |         4 |
|        0001 |          3 |         1 |
|        0001 |          4 |         1 |
|        0001 |          5 |         1 |
|        0002 |          1 |         2 |
|        0002 |          2 |         1 |
|        0002 |          4 |         1 |
|        0002 |          6 |         3 |
+-------------+------------+-----------+

Now i want to show only the max number for every lecturer, my code is:

SELECT it.`id-lecturer`, it.`id-subject`, MAX(it.`exams-num`) AS `exams-number`  
FROM (  
   SELECT e.`id-lecturer`, e.`id-subject`, COUNT(e.`id-lecturer`) AS `exams-num`  
   FROM egzaminy e  
   JOIN subjects s ON e.`id-subject`=s.`id-subject`  
   JOIN lecturers l ON e.`id-lecturer`=l.`id-lecturer`  
   GROUP BY e.`id-lecturer`, e.`id-subject`) it
GROUP BY it.`id-lecturer`

output:

+-------------+------------+--------------+
| id-lecturer | id-subject | exams-number |
+-------------+------------+--------------+
|        0001 |          1 |            4 |
|        0002 |          1 |            3 |
|        0003 |          1 |            2 |
|        0004 |          1 |            5 |
|        0005 |          2 |            1 |
+-------------+------------+--------------+

I get the correct numbers of the max values for each lecturer, but the subjects id doesn't match, it always takes the first row's id. How can I make it to match correctly these two fields in every row?

Thanks for the advice. Fortunately this is my university lecturers database. — jaryn93, Dec 01 '15 at 14:11
Possible duplicate of [SQL Select only rows with Max Value on a Column](http://stackoverflow.com/questions/7745609/sql-select-only-rows-with-max-value-on-a-column) — Strawberry, Dec 01 '15 at 14:23
In which case, consider following this simple two-step course of action: 1. If you have not already done so, provide proper CREATE and INSERT statements (and/or an sqlfiddle) so that we can more easily replicate the problem. 2. If you have not already done so, provide a desired result set that corresponds with the information provided in step 1. — Strawberry, Dec 01 '15 at 14:39

score 0 · Answer 1 · answered Dec 01 '15 at 14:12

0

I guess you can simply use the same query for further conditions like below.

Select t.Lecturer_id,max(t.exams-num) from
    (SELECT e.id-lecturer as Lecturer_id, e.id-subject as Subject_id,
            COUNT(e.id-lecturer) AS exams-num
    FROM exams e    
    JOIN subjects s ON e.id-subject=s.id-subject  
    JOIN lecturers l ON e.id-lecturer=l.id-lecturer  
    GROUP BY e.id-lecturer, e.id-subject ) as t
    group by t.Lecturer_id

answered Dec 01 '15 at 14:12

Arun Palanisamy

5,281
6
28
53

Thanks, I have already changed it, however my problem is still not solved – jaryn93 Dec 01 '15 at 14:22

SQL Select rows with max value from joined table

1 Answers1