Solved

Group By and Order By in same MySQL statement not working

Posted on 2011-02-21
15
420 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
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 40

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
 
LVL 1

Author Comment

by:cbielich
ID: 34949245
errors out
0
 
LVL 40

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 40

Expert Comment

by:Sharath
ID: 34949547
Are you able to execute without Order By clause?
0
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 
LVL 1

Author Comment

by:cbielich
ID: 34949834
same error
0
 
LVL 49

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 40

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 49

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 49

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Constructing a Query Using Escaped Variables 3 42
SQL inner join confusion 15 42
[MYSQL]: Delete is very slow 4 52
mcrypt_create_iv() is deprecated 4 67
More Fun with XML and MySQL – Parsing Delimited String with a Single SQL Statement Are you ready for another of my SQL tidbits?  Hopefully so, as in this adventure, I will be covering a topic that comes up a lot which is parsing a comma (or other…
Popularity Can Be Measured Sometimes we deal with questions of popularity, and we need a way to collect opinions from our clients.  This article shows a simple teaching example of how we might elect a favorite color by letting our clients vote for …
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.
A short film showing how OnPage and Connectwise integration works.

932 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