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.