Search Result with where Clause and group by and Order By desc

Question

Here is my table structure

message_id sender_id receiver_id message
-----------------------------------------
   1         2          1         Hi 1
   2         1          2         Hi 2
   3         2          1         Hi 3
   4         1          2         Hi 4
   5         3          1         Hi 5

It is my query

 select * 
 from message 
 where receiver_id = '1' 
 GROUP BY sender_id 
 ORDER BY message_id DESC

Result is:

5 Hi 5
1 Hi 1

But My expect result is

5 Hi 5
3 Hi 3

What is the actual query?

The `ORDER BY` doesn't happen first, like you probably are thinking it does. When doing a `GROUP BY` you should only select the column(s) that are in the `GROUP BY` or are used in an aggregate function. Otherwise, the value of an ungrouped column is undefined to a certain extent. That holds true with the ordering as well. — Patrick Q, Apr 30 '19 at 17:25
Also, you should show the code that is creating your output. Right now, it isn't clear what columns are being shown (although we can guess, but I'd rather not) — Patrick Q, Apr 30 '19 at 17:26
Actually your output doesnt match your sample data, you have 3 sender with receiver_id =1 ergo you should have 3 rows in your result — Juan Carlos Oropeza, Apr 30 '19 at 17:27
Possible duplicate of [MYSQL GROUP BY and ORDER BY NOT WORKING TOGETHER](https://stackoverflow.com/questions/31157034/mysql-group-by-and-order-by-not-working-together) — Patrick Q, Apr 30 '19 at 17:30
@JuanCarlosOropeza Not for the specified `receiver_id`. There's 2-1, 2-1, and 3-1. — Patrick Q, Apr 30 '19 at 17:31
@A.Developers See also: https://stackoverflow.com/questions/14770671/mysql-order-by-before-group-by — Patrick Q, Apr 30 '19 at 17:36
By reviewing the linked questions/answers and attempting something based on them. You should also indicate what the exact logic is for reaching your expected result. It's good that you're telling us what you expect, but without knowing _why_ you expect that, or _how_ you expect to get it, that's only half the story. — Patrick Q, Apr 30 '19 at 17:57

score 0 · Accepted Answer · answered Apr 30 '19 at 19:49

In your situation below SQL can provide your result. But check the performance for large data.

MAX will return 1 row for each grouped value.First we get message_id after that we get message from join query.So:

SELECT tb1.message_id, 
       tb1.message 
FROM   message tb1 
       INNER JOIN(SELECT Max(message_id) AS mid 
                  FROM   message 
                  WHERE  receiver_id = 1 
                  GROUP  BY sender_id) AS tb2 
               ON tb1.message_id = tb2.mid

From the MySQL Docs:

MySQL extends the use of GROUP BY so that the select list can refer to nonaggregated columns not named in the GROUP BY clause. ... You can use this feature to get better performance by avoiding unnecessary column sorting and grouping. However, this is useful primarily when all values in each nonaggregated column not named in the GROUP BY are the same for each group. The server is free to choose any value from each group, so unless they are the same, the values chosen are indeterminate. Furthermore, the selection of values from each group cannot be influenced by adding an ORDER BY clause. Sorting of the result set occurs after values have been chosen, and ORDER BY does not affect which values the server chooses.

Thanx, It's really helped me. – A.Developer May 01 '19 at 08:06 — A.Developer, May 01 '19 at 08:06

Search Result with where Clause and group by and Order By desc

1 Answers1