CODEWITHSUNDEEP
mysqlsql
Viralk
Viralk

Reputation: 2389

How to Get Data in Descending order using group by in mysql

I want table records as follows:

---------------------------------------------------------------------------
id | receiver_user_id | sender_user_id | message_text | insertdate
---------------------------------------------------------------------------
6  | 33               | 42             | OLD          | 2013-05-08 10:08:53
8  | 33               | 43             | ddddd        | 2013-05-07 19:01:45
7  | 33               | 38             | Ipsum        | 2013-04-25 10:27:35
---------------------------------------------------------------------------

I am using this query :

SELECT *
FROM (`player_messages`)
WHERE `receiver_user_id` =  '33'
GROUP BY sender_user_id
ORDER BY `player_messages`.`insertdate` DESC

And get table records like below.

---------------------------------------------------------------------------
id | receiver_user_id | sender_user_id | message_text | insertdate
---------------------------------------------------------------------------
6  | 33               | 43             | ddddd        | 2013-05-07 19:01:45
4  | 33               | 42             | NEW          | 2013-05-07 10:08:58
7  | 33               | 38             | Ipsum        | 2013-04-25 10:27:35
---------------------------------------------------------------------------

Please help. Thanks in advance.

Upvotes: 0

Views: 45036

Answers (2)

fthiella
fthiella

Reputation: 49089

You are selecting all fields from your table SELECT * but you are only grouping by sender_user_id, and the values of non-aggregated columns will be untedermined and not affected by an order by.

You should write your query like this:

SELECT   player_messages.*
FROM     player_messages
WHERE    (receiver_user_id, sender_user_id, insertdate) IN (
           SELECT   receiver_user_id, sender_user_id, MAX(insertdate)
           FROM     player_messages
           WHERE    receiver_user_id='33'
           GROUP BY receiver_user_id, sender_user_id)
ORDER BY player_messages.insertdate DESC

This will return all the rows of player_messages that have the maximum insertdate for every combination of receiver and sender.

Upvotes: 5

Lithu T.V
Lithu T.V

Reputation: 20021

When you query your MySQL database, you can sort the results by any field in an ascending or descending order by just adding 'ORDER BY' at the end of your query. You would use ORDER BY field_name ASC for an ascending sort, or ORDER BY field_name DESC for a descending sort.

eg

"SELECT * FROM address ORDER BY name ASC"

Upvotes: -3

Related Questions