通过不使用group by子句来排序

Question

I am using corequery of mysql in cakephp. I want the records in descending order. This is my table structure

enter code here
$coreQueryUser = $this->Message->query(
            "select * from messages where messages.list_id = 3 
            group By (if(sender_id > reciever_id,  sender_id, reciever_id)), 
                (if(sender_id > reciever_id,  reciever_id, sender_id))
            order by id desc
            "
        );

I want last message that belongs to (sender_id and reciver_id and viceversa) that belongs to list id 3

when i run this query i get the following output

<pre>Array
(
[0] => Array
    (
        [messages] => Array
            (
                [id] => 1
                [sender_id] => 21
                [reciever_id] => 10
                [list_id] => 3
                [message] => hello
                [add_date] => 2016-09-25 00:00:00
                [is_check] => 0
            )

    )

[1] => Array
    (
        [messages] => Array
            (
                [id] => 3
                [sender_id] => 22
                [reciever_id] => 10
                [list_id] => 3
                [message] => hello s
                [add_date] => 2016-09-25 16:39:41
                [is_check] => 0
            )

    )

)

but i wnat result like that:

Array
(
    [0] => Array
        (
            [messages] => Array
                (
                    [id] => 2
                    [sender_id] => 10
                    [reciever_id] => 21
                    [list_id] => 3
                    [message] => hello sir
                    [add_date] => 2016-09-25 00:00:00
                    [is_check] => 0
                )

    )

[1] => Array
    (
        [messages] => Array
            (
                [id] => 6
                [sender_id] => 22
                [reciever_id] => 10
                [list_id] => 3
                [message] => new
                [add_date] => 2016-09-25 16:39:41
                [is_check] => 0
            )

    )

)

Can anyone help me :(

Answer 1

The problem is that your query is against the sql standard because you have several fields in the select list that are neither in the group by list, nor are subject of an aggregate function, such as sum(). MySQL unfortunately allows such invalid queries to run under certain sql mode settings (the default settings of the most recent versions of MySQL would prevent such queries from running).

As MySQL documentation on group by clause says (bolding is mine):

If ONLY_FULL_GROUP_BY is disabled, a MySQL extension to the standard SQL use of GROUP BY permits the select list, HAVING condition, or ORDER BY list to refer to nonaggregated columns even if the columns are not functionally dependent on GROUP BY columns. This causes MySQL to accept the preceding query. In this case, the server is free to choose any value from each group, so unless they are the same, the values chosen are indeterminate, which is probably not what you want. Furthermore, the selection of values from each group cannot be influenced by adding an ORDER BY clause. Result set sorting occurs after values have been chosen, and ORDER BY does not affect which value within each group the server chooses.

You apparently want the latest record (with max(id) for each group. The proper way is to have a subquery that returns the max(id) per group and in the outer query join back to your main table using the ids to get the value of the other fields:

select m.*
from
messages m
inner join (
            select max(id) as maxid
            from messages
            where messages.list_id = 3 
            group By (if(sender_id > reciever_id,  sender_id, reciever_id)), 
            (if(sender_id > reciever_id,  reciever_id, sender_id))
           ) t1 on m.id=t1.maxid