-2

I'm trying to find the best 5 students from 2016 and 2017. My query right now looks like this:

SELECT  S.vorname, 
        S.nachname, 
        MAX(I.abgeschlossen) AS Abschlussjahr, 
        A.note
FROM student AS S
INNER JOIN inskription AS I ON S.matnr= I.student
INNER JOIN absolvierung AS A ON S.matnr = A.student
WHERE A.note = 1
AND YEAR(I.abgeschlossen) IN ('2016', '2017')
GROUP BY S.vorname, S.nachname, A.note
ORDER BY Abschlussjahr DESC

and the output looks like this:

picture of my output

What is a possible way to have the top 5 from 2016 and 2017 in the same column?

Ergest Basha
  • 7,870
  • 4
  • 8
  • 28
javaScriptooooo
  • 1
  • 2
  • Why do you not simply add `LIMIT 5` to whatever you tried to get the TOP x ? (How to use that LIMIT is described on this page: [SELECT](https://dev.mysql.com/doc/refman/8.0/en/select.html)) – Luuk Jun 15 '22 at 19:42
  • @Luuk If i add LIMIT 5 i will only get the results from 2017 but not from 2016. Or is there a way i could achieve this? – javaScriptooooo Jun 15 '22 at 19:46
  • Remove the `ORDER BY Abschlussjahr DESC` ? But it's pretty unclear from which value you want the get the MAX/TOP, because I do not know what kind of data is in the table `absolvierung` – Luuk Jun 15 '22 at 19:49
  • @Luuk i'm sorry i didn't ask precisely enough. This is my first time asking a question. So i want the Top 5 Students with the highest dates. 5 students from 2017 and 5 students from 2016, so in total all 10. – javaScriptooooo Jun 15 '22 at 19:59
  • `2016` and `2017` are integers not string. Remove the quotes. – Shmiel Jun 15 '22 at 20:02

1 Answers1

1

This should return you the top 5 for both years

(SELECT S.vorname, 
        S.nachname, 
        I.abgeschlossen AS Abschlussjahr, 
        A.note
FROM student AS S
INNER JOIN inskription AS I ON S.matnr= I.student
INNER JOIN absolvierung AS A ON S.matnr = A.student
WHERE A.note = 1
AND YEAR(I.abgeschlossen) = 2016
ORDER BY Abschlussjahr DESC
LIMIT 5)
UNION
(SELECT S.vorname, 
        S.nachname, 
        I.abgeschlossen AS Abschlussjahr, 
        A.note
FROM student AS S
INNER JOIN inskription AS I ON S.matnr= I.student
INNER JOIN absolvierung AS A ON S.matnr = A.student
WHERE A.note = 1
AND YEAR(I.abgeschlossen) = 2017
ORDER BY Abschlussjahr DESC
LIMIT 5)

If you want to order by some other columns just change the column name.

Also the group by is unnecessary as you don't need the max() aggregate function.

Edit: Found on this post that you should add parenthesis

Shmiel
  • 1,201
  • 10
  • 25