MYSQL - GROUP BY and ORDER BY question

I have this query:

SELECT id, rdate, member_id
FROM msgs
WHERE wrote_to='1000'
AND tb1.member_id != '1002'
AND deleted != '1000'
GROUP BY member_id
ORDER BY rdat开发者_Python百科e DESC

The problem is I get the messages but not the new one by rdate...

How can I order them by rdate and group by member_id ?... so I will get the newest message of each user?

Thanks.

Try:

SELECT * FROM (
      SELECT id, rdate, member_id FROM msgs 
       WHERE wrote_to = '1000' 
         AND tb1.member_id != '1002' 
         AND deleted != '1000' 
    ORDER BY rdate DESC
) tmp_msgs
GROUP BY member_id

This will have a little less performance, because of the temporary table, but that's a trade off you have to make for a working solution.

As @Brendan Bullen has noticed, why are you using the alias tb1. there?

Assuming a sequential ID:

SELECT id, rdate, member_id
FROM msgs
WHERE wrote_to='1000'
AND tb1.member_id != '1002'
AND deleted != '1000'
GROUP BY member_id
HAVING id = MAX(id)
ORDER BY rdate DESC

Using GROUP BY on a nested select is not reliable (even on ordered results) as the behaviour for columns not specified in the grouping is undefined.

rdate may be coming from another table or else you entered wrong coluomn name.