MySQL: Order grouped results by ID

Question

I have a little problem to sort my results by ID when I place grouped:

$get_seen_messages = mysql_query("SELECT * 
                                  FROM chat 
                                  WHERE userto='$login_cookie' 
                                    AND status='seen' 
                                  GROUP BY userfrom 
                                  ORDER BY id DESC");

Please help me :D

Every time you use [the `mysql_`](http://stackoverflow.com/questions/12859942/why-shouldnt-i-use-mysql-functions-in-php) database extension in new code **[a Kitten is strangled somewhere in the world](http://2.bp.blogspot.com/-zCT6jizimfI/UjJ5UTb_BeI/AAAAAAAACgg/AS6XCd6aNdg/s1600/luna_getting_strangled.jpg)** it is deprecated and has been for years and is gone for ever in PHP7. If you are just learning PHP, spend your energies learning the `PDO` or `mysqli` database extensions. [Start here](http://php.net/manual/en/book.pdo.php) — RiggsFolly, Sep 24 '16 at 22:45
Your script is at risk of [SQL Injection Attack](http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php) Have a look at what happened to [Little Bobby Tables](http://bobby-tables.com/) Even [if you are escaping inputs, its not safe!](http://stackoverflow.com/questions/5741187/sql-injection-that-gets-around-mysql-real-escape-string) Use [prepared parameterized statements](http://php.net/manual/en/mysqli.quickstart.prepared-statements.php) — RiggsFolly, Sep 24 '16 at 22:46

score 1 · Answer 1 · answered Sep 24 '16 at 22:46

Which id? You should never use SELECT * with GROUP BY. When you use GROUP BY, all unaggregated columns should be in the GROUP BY. So, this is a correctly formed query

SELECT userfrom, MAX(id) as id
FROM chat
WHERE userto = '$login_cookie' AND status = 'seen'
GROUP BY userfrom
ORDER BY MAX(id) DESC;

I suspect, though, that you want the most recent row from chat for each userfrom. In that case, a correctly formed query looks like:

select c.*
from chat c
where userto = '$login_cookie' and status = 'seen' and
      id = (select max(c2.id)
            from chat c2
            where c2.userto = c.userto and c2.status = c.status
           );

score 0 · Answer 2 · answered Sep 24 '16 at 23:12

Have you tried with subquerys?

$get_seen_messages = mysql_query("
    SELECT * FROM
        (SELECT * 
            FROM chat 
            WHERE userto='$login_cookie' 
            AND status='seen' 
            GROUP BY userfrom) as subquery 
    ORDER BY id DESC");

MySQL: Order grouped results by ID

2 Answers2