Solved

Group By and Order By in same MySQL statement not working

Posted on 2011-02-21
15
423 Views
Last Modified: 2012-05-11
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
Comment
Question by:cbielich
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 4
  • 3
  • +1
15 Comments
 
LVL 22

Expert Comment

by:Om Prakash
ID: 34949022
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
 
LVL 1

Author Comment

by:cbielich
ID: 34949036
I have several fields that I need to pull, can I add them all?
0
 
LVL 41

Expert Comment

by:Sharath
ID: 34949191
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 Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 
LVL 1

Author Comment

by:cbielich
ID: 34949245
errors out
0
 
LVL 41

Expert Comment

by:Sharath
ID: 34949368
Can you post your error message?
0
 
LVL 1

Author Comment

by:cbielich
ID: 34949379

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

Open in new window

0
 
LVL 41

Expert Comment

by:Sharath
ID: 34949547
Are you able to execute without Order By clause?
0
 
LVL 1

Author Comment

by:cbielich
ID: 34949834
same error
0
 
LVL 52

Expert Comment

by:Ryan Chong
ID: 34950097
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
 
LVL 1

Author Comment

by:cbielich
ID: 34955510
Pulls up the record the same as my script, no order to it
0
 
LVL 41

Expert Comment

by:Sharath
ID: 34955923
What is your MySQL version? Are you getting error without ORDER BY clause also?
0
 
LVL 1

Author Comment

by:cbielich
ID: 34956424
Server Version: 5.0.91
No Error without Order By Clause
0
 
LVL 52

Accepted Solution

by:
Ryan Chong earned 500 total points
ID: 34956975
Timestamp is a reserved word in mysql?
0
 
LVL 1

Author Comment

by:cbielich
ID: 34957223
dont think so I use it all the time, I also tried using ID as order by that did not work.
0
 
LVL 52

Expert Comment

by:Ryan Chong
ID: 34961137
pls also post the scripts including part of mysql_fetch_array() so that we got better clues to resolve your problem.
0

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

All XML, All the Time; More Fun MySQL Tidbits – Dynamically Generate XML via Stored Procedure in MySQL Extensible Markup Language (XML) and database systems, a marriage we are seeing more and more of.  So the topics of parsing and manipulating XM…
Introduction This article is intended for those who are new to PHP error handling (https://www.experts-exchange.com/articles/11769/And-by-the-way-I-am-New-to-PHP.html).  It addresses one of the most common problems that plague beginning PHP develop…
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…

732 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question