Search code examples
mysqlsqlphpmyadmineasyphp

SQL unexpected return

So basically, I have a simple Database with only one table (it's a test DB). The table has 4 columns:

  • ID
  • Name
  • OralGrade
  • WrittenGrade

What I'm trying to do is pretty simple (that's why i'm asking for your help): I want to get the name and average of the student whith the highest average.

What i tried:

SELECT nom, MAX(avg) 
    FROM ( 
        SELECT nom, (noteOrale + noteEcrit)/2 as avg 
        FROM etudiant 
        GROUP BY nom) AS Table;

After trying this query, it returned me the name and an average but the average doesn't correspond to the name.

Can someone give me pointers or explain what went wrong? Thanks

Solution

  • Use order by and limit. No subquery is necessary:

        SELECT nom, (noteOrale + noteEcrit)/2 as avg 
    FROM etudiant 
    ORDER BY avg DESC
    LIMIT 1;

    It would appear that no GROUP BY is needed either, because the values are all on one row.

    If they are multiple rows, then you need GROUP BY.