Group By and Order By in same MySQL statement not working

Here is my query:

$sql = "SELECT * FROM messages LEFT JOIN users ON messages.from_id = users.ID WHERE messages.to_id = '$session_user_id' AND messages.hide != '$session_user_id' GROUP BY messages.message_id ORDER BY messages.timestamp DESC";

I already know that using Group By and Order By in the same statement does not work, I have read some solutions but can seem to rewrite is successfully to make it work, can someone help me please?

I know an INNER JOIN needs to be in there, but since I am already using a LEFT JOIN I am having a hardtime using the two different joins in the same statement.
LVL 1
cbielichAsked:
Who is Participating?
 
Ryan ChongConnect With a Mentor Commented:
Timestamp is a reserved word in mysql?
0
 
Om PrakashCommented:
instead of *, specify the field name that will be part of group.

Example:
select title, count(*) AS Number
from employee
GROUP BY title
ORDER BY Number;
0
 
cbielichAuthor Commented:
I have several fields that I need to pull, can I add them all?
0
Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

 
SharathData EngineerCommented:
Can you try this?
$sql = "SELECT * FROM (SELECT * FROM messages LEFT JOIN users ON messages.from_id = users.ID WHERE messages.to_id = '$session_user_id' AND messages.hide != '$session_user_id' GROUP BY messages.message_id) as t1 ORDER BY timestamp DESC";

Open in new window

0
 
cbielichAuthor Commented:
errors out
0
 
SharathData EngineerCommented:
Can you post your error message?
0
 
cbielichAuthor Commented:

Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in

Open in new window

0
 
SharathData EngineerCommented:
Are you able to execute without Order By clause?
0
 
cbielichAuthor Commented:
same error
0
 
Ryan ChongCommented:
try:

$sql = "SELECT * FROM messages LEFT JOIN users ON messages.from_id = users.ID WHERE messages.to_id = '$session_user_id' AND messages.hide <> '$session_user_id' GROUP BY messages.message_id ORDER BY messages.timestamp DESC";

?
0
 
cbielichAuthor Commented:
Pulls up the record the same as my script, no order to it
0
 
SharathData EngineerCommented:
What is your MySQL version? Are you getting error without ORDER BY clause also?
0
 
cbielichAuthor Commented:
Server Version: 5.0.91
No Error without Order By Clause
0
 
cbielichAuthor Commented:
dont think so I use it all the time, I also tried using ID as order by that did not work.
0
 
Ryan ChongCommented:
pls also post the scripts including part of mysql_fetch_array() so that we got better clues to resolve your problem.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.