Solved

Group By and Order By in same MySQL statement not working

Posted on 2011-02-21
15
421 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
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
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
 
LVL 1

Author Comment

by:cbielich
ID: 34949834
same error
0
 
LVL 50

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 50

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 50

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

Suggested Solutions

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…
This guide whil teach how to setup live replication (database mirroring) on 2 servers for backup or other purposes. In our example situation we have this network schema (see atachment). We need to replicate EVERY executed SQL query on server 1 to…
This Micro Tutorial will give you a basic overview how to record your screen with Microsoft Expression Encoder. This program is still free and open for the public to download. This will be demonstrated using Microsoft Expression Encoder 4.
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.

773 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