MySql:Order by 和 Group By 组合不提供最新记录 [英] MySql : Order by and Group By combining not giving the latest record

问题描述

使用以下查询，我正在寻找一种解决方案来获取具有某些条件的最新记录.但它给了我第一条记录，而不是最新记录.我认为它只考虑组请指教

Using the following query I am looking for a solution to get the latest record that having some conditions. But it gives me the first record, not the latest. I think its only considering the group by Please advise me

SELECT * FROM `contacts` WHERE `id_receiver`=1  GROUP BY `id_thread` ORDER BY created DESC

id   id_sender   id_thread sender_email   id_receiver      created(datetime)
1    2             2         51             1                2012-03-24 13:44:48
2    4             4         1              5                 2012-04-26 13:46:05
3    2             2         51             1                2012-04-09 12:12:30

所需的输出

id   id_sender  id_thread sender_email   id_receiver      created(datetime)
3    2           2         51             1                2012-04-09 12:12:30

我做了一个测试，只是交换了 order by 和 group by ，给了我一个错误.

I had done a test just swap the order by and group by , giving me an erros.

有人可以看看这个吗?谢谢.

Anybody can just look in to this?. Thanks.

EDIT 编辑问题，忘记写 id_thread

EDIT Edited question, forget to write id_thread

推荐答案

当您的表中没有 id_thread 列时，您如何GROUP BY id_thread?

How can you GROUP BY id_thread when there is no id_thread column in your table?

SELECT * 
FROM contacts 
WHERE id_receiver = 1 
                                 --- GROUP BY id_thread
                                 --- removed 
ORDER BY created DESC
LIMIT 1                          --- only show one row

<小时>

根据您的评论，您想要的是每个 id_thread 的最新(按 created 排序)行，这是一个不同且更复杂的查询.这种查询甚至还有一个标签，名为 [greatest-n-per-group].

---

Based on your comments, what you want is the latest (ordered by created) row for every id_thread, which is a different and more complex query. There's even a tag for this kind of queries, named [greatest-n-per-group].

SELECT c.* 
FROM contacts AS c
  JOIN
    ( SELECT id_thread, MAX(created) AS created 
      FROM contacts 
      WHERE id_receiver = 1  
      GROUP BY id_thread
    ) AS g 
    ON (g.id_thread, g.created) = (c.id_thread, c.created) 
WHERE c.id_receiver = 1  

这篇关于MySql:Order by 和 Group By 组合不提供最新记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！