CODEWITHSUNDEEP
mysqlsqlmax
Madison Scodelario
Madison Scodelario

Reputation: 97

How to get multiple values of a column based on max value of another column?

So I have this table

enter image description here

I want to "print" the Surnames of those who are in the same semester but also their grade is maximum 2 points below their semester's highest grade. For example for the semester 4 the surnames needed are "Παπαβασιλείου" (highest grade = 9) and "Βαλκανιώτης" (grade = 7 >= (9 - 2))

what I've done is this code but it does not satisfy my criteria 

SELECT GRADE as GRADE, SURNAME FROM pinakas2 
WHERE GRADE >= ((SELECT MAX(GRADE) FROM pinakas2) - 2) 
GROUP BY SEMESTER ORDER BY GRADE DESC

It gives as a result this:

enter image description here

Upvotes: 0

Views: 48

Answers (2)

jarlh
jarlh

Reputation: 44696

Remove the GROUP BY, do ORDER BY semester instead:

SELECT GRADE as GRADE, SURNAME
FROM pinakas2 
WHERE GRADE >= ((SELECT MAX(GRADE) FROM pinakas2) - 2) 
ORDER BY SEMESTER, GRADE DESC

If you want highest grade per semester minus 2, do a correlated sub-query instead:

SELECT GRADE as GRADE, SURNAME
FROM pinakas2 p1
WHERE GRADE >= ((SELECT MAX(GRADE) FROM pinakas2 p2
                 where p1.semester = p2.semester) - 2) 
ORDER BY SEMESTER, GRADE DESC

Upvotes: 1

O. Jones
O. Jones

Reputation: 108641

First you'll need a subquery to find out the highest grade in the semester, as follows:

        SELECT MAX(GRADE) GRADE, SEMESTER
          FROM pinakas2
         GROUP BY SEMESTER

Then you need to use that to retrieve the students you want:

  SELECT a.GRADE, a.SURNAME, a.SEMESTER
    FROM pinakas2 a
    JOIN (
        SELECT MAX(GRADE) GRADE, SEMESTER
          FROM pinakas2
         GROUP BY SEMESTER
         ) b ON a.SEMESTER = b.SEMESTER AND a.GRADE >= b.GRADE - 2
   ORDER BY a.SURNAME, a.GRADE

If for some reason you don't care about the semester in which this occurred, do this instead.

  SELECT DISTINCT a.GRADE, a.SURNAME
    FROM pinakas2 a
    JOIN (
        SELECT MAX(GRADE) GRADE, SEMESTER
          FROM pinakas2
         GROUP BY SEMESTER
         ) b ON a.SEMESTER = b.SEMESTER AND a.GRADE >= b.GRADE - 2
   ORDER BY a.SURNAME, a.GRADE

Upvotes: 0

Related Questions