Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Group By and Order By in same MySQL statement not working

Posted on 2011-02-21
15
Medium Priority
?
426 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 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.

 
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 53

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 53

Accepted Solution

by:
Ryan Chong earned 2000 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 53

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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
In this blog post, we’ll look at how ClickHouse performs in a general analytical workload using the star schema benchmark test.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
Suggested Courses

609 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