Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 429
  • Last Modified:

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.
0
cbielich
Asked:
cbielich
  • 7
  • 4
  • 3
  • +1
1 Solution
 
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
 
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
NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

 
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
 
Ryan ChongCommented:
Timestamp is a reserved word in mysql?
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

Featured Post

Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

  • 7
  • 4
  • 3
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now