分组依据和订购依据 [英] GROUP BY and ORDER BY

问题描述

可能重复:
GROUP BY中的SQL ORDER BY总数

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

将无法正常工作.我想做的是分组到dialog_id.然后，所有具有dialog_id的对象都按日期排序，然后按日期对所有对象排序.

所以:

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

dialog_id的两个条目的值为1(使用GROUP BY).好的.然后，它应该按具有最新日期的两个条目之一(在组内)进行排序(按日期desc排序).这是日期为1289158931的情况.

这怎么办?

更新:

我想出来的东西:

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

它为我提供了每个dialog_id的最后一个，这就是为什么我要将其分组.

解决方案

使用:

  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

...或:

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)

Possible Duplicate:
SQL ORDER BY total within GROUP BY

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

Wont work properly. What i want to do is group in to dialog_id.. Then all with the dialog_id sort them by date, and then sort them by date for all..

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

The two entry´s of the dialog_id gets 1 (With GROUP BY). OK. And then it should order by the one of the two entrys(inside the group) that have the newest date(order by date desc). Which this case is the one with date 1289158931.

How can this be done?

UPDATE:

What i want to come out:

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

It give me the last for each dialog_id, thats why i want it grouped in.

解决方案

Use:

  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)

这篇关于分组依据和订购依据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！