MySQL: how is a GROUP BY field for extracting rows with ORDER BY another field?

Question

accept the 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 every day, something like this:

Desired Result:

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

I thought the 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 occurs before ORDER BY, so it does not see and cannot apply ORDER.

so how can i apply ORDER on GROUP BY?

Note: please keep in mind that I need the simplest query because my query is actually very complex, I know that I can achieve this result with some subqueries or JOINing, but I want to know how to apply ORDER to GROUP BY. thank

Answer 1

Oracle , SQL mysql ( to_date mysql). , , .

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

:

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

Answer 2

, , GROUP/ORDER, . - - :

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

, .