Elliott Addi Elliott Addi - 1 year ago 60
MySQL Question

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

Answer Source

Use order by and limit. No subquery is necessary:

    SELECT nom, (noteOrale + noteEcrit)/2 as avg 
    FROM etudiant 
    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.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download