GROUP BY and ORDER BY

Question

Possible duplicate:
SQL ORDER BY total in GROUP BY

SELECT *
FROM users_pm_in
WHERE uID = '1'
GROUP BY dialog_id
ORDER BY date DESC

Not working fine. What I want to do is group in to dialog_id .. Then everyone with the help of dialog_id sort them by date and then sort by date for everyone.

So:

id | uID | bID | msg     | dialog_id | date
--------------------------------------------------
 1 | 1   | 2   | Hello   | 1         | 1289158631
 2 | 2   | 1   | Hi?     | 1         | 1289158691
 3 | 1   | 2   | Wazzaa? | 1         | 1289158931

Two dialog_id entries get 1 (with GROUP BY). OK. And then he must order one of two records (within the group) that have the most recent date (order by date in descending order). Which this case is with date 1289158931.

How can I do that?

UPDATE:

What I want to release:

while($row = mysql_fetch_array($query)){
    echo $row["msg"] // it should echo "Wazzaa?"
    echo $row["id"] // it should give me id 3
}

This gives me the last for each dialog_id, which is why I want it to group.

Answer 1

Using:

  SELECT a.id, 
         a.msg
    FROM USERS_PM a
   WHERE a.dialog_id = 1
     AND 1 IN (a.uid, a.bid)
ORDER BY a.date DESC
   LIMIT 1

... or:

SELECT a.id, 
       a.msg
  FROM USERS_PM a
  JOIN (SELECT t.dialog_id,
               MAX(t.date) AS max_date
          FROM USERS_PM t
      GROUP BY t.dialog_id) b ON b.dialog_id = a.dialog_id
                             AND b.max_date = a.date
 WHERE a.dialog_id = 1
   AND 1 IN (a.uid, a.bid)