我有下表:
id时间文字otheridentifier --------- ---------------------------------- 1 6 apple 4 2 7 orange 4 3 8香蕉3 4 9梨3 5 10葡萄2我想要做的是选择3个最近的记录(按时间desc),其 otheridentifier s是不同的。因此,在这种情况下,结果将是 id 的:5,4和2。
id = 3将会被跳过,因为有一个更新的记录具有相同的 otheridentifier 字段。
下面是我试图做的:
SELECT * FROM`table` GROUP BY(`otheridentifier` )ORDER BY`time` DESC LIMIT 3然而,我最终得到 id = 5, 3 和 1 ,而不是像预期的那样使用5,4,2。
有人能告诉我为什么这个查询不会返回我预期的结果吗?我尝试将ORDER BY更改为ASC,但是这只是将返回的行重新排列为1,3,5。 t返回您期望的结果,因为在排序之前会发生分组,这反映在SQL语句中子句的位置。不幸的是你不得不花更多的时间来获得你想要的行。试试这个: $ p $ SELECT * FROM`table` WHERE`id` =( SELECT`id` FROM`table` as`alt` WHERE`alt`.`otheridentifier` =`table`.`otheridentifier` ORDER BY`time` DESC LIMIT 1 ) ORDER BY`time` DESC LIMIT 3
I have the following table:
id time text otheridentifier ------------------------------------------- 1 6 apple 4 2 7 orange 4 3 8 banana 3 4 9 pear 3 5 10 grape 2What I want to do is select the 3 most recent records (by time desc), whose otheridentifiers are distinct. So in this case, the result would be id's: 5, 4, and 2.
id = 3 would be skipped because there's a more recent record with the same otheridentifier field.
Here's what I tried to do:
SELECT * FROM `table` GROUP BY (`otheridentifier`) ORDER BY `time` DESC LIMIT 3However, I end up getting rows of id = 5, 3, and 1 instead of 5, 4, 2 as expected.
Can someone tell me why this query wouldn't return what I expected? I tried changing the ORDER BY to ASC but this simply rearranges the returned rows to 1, 3, 5.
解决方案It doesn't return what you expect because grouping happens before ordering, as reflected by the position of the clauses in the SQL statement. You're unfortunately going to have to get fancier to get the rows you want. Try this:
SELECT * FROM `table` WHERE `id` = ( SELECT `id` FROM `table` as `alt` WHERE `alt`.`otheridentifier` = `table`.`otheridentifier` ORDER BY `time` DESC LIMIT 1 ) ORDER BY `time` DESC LIMIT 3
更多推荐
选择一列不同值的3个最新记录
发布评论