CODEWITHSUNDEEP
mysqlselectgroup-bysql-order-by
Alexar
Alexar

Reputation: 1878

MySQL: How to GROUP BY a field to retrieve the rows with ORDER BY another field?

assume following data:

Data:

    id  |   date        |   name    |   grade
--------+---------------+-----------+---------------
    1   | 2010/12/03    |   Mike    |   12
    2   | 2010/12/04    |   Jenny   |   12
    3   | 2010/12/04    |   Ronald  |   15
    4   | 2010/12/03    |   Yeni    |   11

i want to know who has the best grade in each day, something like this:

Desired Result:

    id  |   date        |   name    |   grade
--------+---------------+-----------+---------------
    1   | 2010/12/03    |   Mike    |   12
    3   | 2010/12/04    |   Ronald  |   15

i thought query should look like this:

SELECT name FROM mytable
GROUP BY date
ORDER BY grade DESC

but it returns something like this:

Current Unwanted Result:

    id  |   date        |   name    |   grade
--------+---------------+-----------+---------------
    1   | 2010/12/03    |   Mike    |   12
    2   | 2010/12/04    |   Jenny   |   12

i searched and i found the reason:

GROUP BY happens before ORDER BY so it does not see and can't apply ORDER.

so how can i apply ORDER on GROUP BY?

Note: please keep in mind that i need the most simple query, because my query is actually very complex, i know i can achieve this result by some subquery or JOINing, but i want to know how to apply ORDER to GROUP BY. thanks

Upvotes: 3

Views: 551

Answers (2)

dcp
dcp

Reputation: 55444

I used Oracle for this example, but the SQL should work in mysql (you may need to tweak the to_date stuff to work with mysql). You really need a subquery here to do what you are asking.

CREATE TABLE mytable (ID NUMBER, dt DATE, NAME VARCHAR2(25), grade NUMBER);

INSERT INTO mytable VALUES(1,to_date('2010-12-03','YYYY-MM-DD'),'Mike',12);
INSERT INTO mytable VALUES(1,to_date('2010-12-04','YYYY-MM-DD'),'Jenny',12);
INSERT INTO mytable VALUES(1,to_date('2010-12-04','YYYY-MM-DD'),'Ronald',15);
INSERT INTO mytable VALUES(1,to_date('2010-12-03','YYYY-MM-DD'),'Yeni',11);

    SELECT id
         , dt
         , name
         , grade
      FROM mytable t1
     WHERE grade = (SELECT max(grade)
                      FROM mytable t2
                     WHERE t1.dt = t2.dt)
    ORDER BY dt

Results:

ID  DT          NAME   GRADE
1   12/3/2010   Mike   12
2   12/4/2010   Ronald 15

Upvotes: 2

faroligo
faroligo

Reputation: 585

I know you said you wanted a GROUP / ORDER only solution but you will need to use a subquery in this instance. The simplest way would be something like this:

SELECT id, date, name, grade
FROM   mytable t1
WHERE grade = 
(SELECT MAX(t2.grade) FROM mytable t2 WHERE t1.id = t2.id)

This would show multiple students if they shared the highest grade for the day.

Upvotes: 0

Related Questions