使用以下查询,我正在寻找一种解决方案来获取具有某些条件的最新记录。但它给了我第一张唱片,而不是最新的。我认为它只考虑小组请告诉我
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
我做了一个测试,只是交换顺序和分组,给了我一个错误。
任何人都可以看看这个?。谢谢。
编辑编辑的问题,忘记写id_thread
当表中没有id_thread
列时,如何GROUP BY id_thread
?
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]
。
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
如果记录是按顺序排列的,那么您也可以按id进行排序-当且仅当它是按顺序创建的-