Solved

Group By and Order By in same MySQL statement not working

Posted on 2011-02-21
15
419 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
  • 7
  • 4
  • 3
  • +1
15 Comments
 
LVL 22

Expert Comment

by:Om Prakash
Comment Utility
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
Comment Utility
I have several fields that I need to pull, can I add them all?
0
 
LVL 40

Expert Comment

by:Sharath
Comment Utility
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
 
LVL 1

Author Comment

by:cbielich
Comment Utility
errors out
0
 
LVL 40

Expert Comment

by:Sharath
Comment Utility
Can you post your error message?
0
 
LVL 1

Author Comment

by:cbielich
Comment Utility

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

Open in new window

0
 
LVL 40

Expert Comment

by:Sharath
Comment Utility
Are you able to execute without Order By clause?
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 1

Author Comment

by:cbielich
Comment Utility
same error
0
 
LVL 49

Expert Comment

by:Ryan Chong
Comment Utility
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
Comment Utility
Pulls up the record the same as my script, no order to it
0
 
LVL 40

Expert Comment

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

Author Comment

by:cbielich
Comment Utility
Server Version: 5.0.91
No Error without Order By Clause
0
 
LVL 49

Accepted Solution

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

Author Comment

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

Expert Comment

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

Featured Post

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Both Easy and Powerful How easy is PHP? http://lmgtfy.com?q=how+easy+is+php (http://lmgtfy.com?q=how+easy+is+php)  Very easy.  It has been described as "a programming language even my grandmother can use." How powerful is PHP?  http://en.wikiped…
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…

772 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now