CODEWITHSUNDEEP
phpmysqldatabasegroup-by
Klaus Jasper
Klaus Jasper

Reputation: 89

PHP - How to Use ORDER BY and GROUP BY together

I created a table that contains message, sender, to, time I want group by sender and order by time this is my code

$query= mysql_query("SELECT * FROM  `table` ORDER BY `time` GROUP BY `sender`")or die(mysql_error());
while($arr = mysql_fetch_array($query)){
$num = mysql_num_rows($query);
$msg = $arr ['message'];
echo '</br>';
echo $msg;
}

that shows me this error

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'GROUP BYsender' at line 1

So how to fix that problem?

Thanks
Klaus

Upvotes: 0

Views: 19278

Answers (3)

Chirag Savaliya
Chirag Savaliya

Reputation: 29

how to use self-joins to get the max/min/something-n rows per group.

In your situation, it can be applied to the effect you want like so:

SELECT * FROM
(SELECT group_id, MAX(`yourdate`) AS yourdate FROM tbl_name GROUP BY group_id)
AS x JOIN tbl_name USING (`group_id`,yourdate);

Upvotes: 0

echo_Me
echo_Me

Reputation: 37243

try this code

 $query= mysql_query("SELECT * FROM  `table`  GROUP BY `sender` ORDER BY `time` ")or  die(mysql_error());
                                             // ^^--will be before order                    
    $num = mysql_num_rows($query);  // out of the while loop
    while($arr = mysql_fetch_array($query)){
    $msg = $arr['message'].'<br />';
           // ^^--remove space here
    echo $msg;
    }

Upvotes: 1

Abhishek Kannan
Abhishek Kannan

Reputation: 988

Note your precedence. After the results have been grouped , it should be ordered. 

"SELECT * FROM  `table`  GROUP BY `sender` ORDER BY `time`"

Upvotes: 2

Related Questions