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.
$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.
ASKER
I have several fields that I need to pull, can I add them all?
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";ASKER
errors out
Can you post your error message?
ASKER
Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in
Are you able to execute without Order By clause?
ASKER
same error
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";
?
$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";
?
ASKER
Pulls up the record the same as my script, no order to it
What is your MySQL version? Are you getting error without ORDER BY clause also?
ASKER
Server Version: 5.0.91
No Error without Order By Clause
No Error without Order By Clause
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
dont think so I use it all the time, I also tried using ID as order by that did not work.
pls also post the scripts including part of mysql_fetch_array() so that we got better clues to resolve your problem.
Example:
select title, count(*) AS Number
from employee
GROUP BY title
ORDER BY Number;