MySQL SQL 选择行，其中 id=max(id)
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7809475/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL select row where id=max(id)
提问by user1001369
I have a table which holds product information with multiple instances of a product with the same title, identified by different colours and their ids. I need to output the entire row where the id = the maximum id grouped by the title, but I can't seem to get it to do this. Here is a very simplified table and some example data:
我有一个表，其中包含具有相同标题的多个产品实例的产品信息，由不同的颜色和它们的 id 标识。我需要输出整行，其中 id = 按标题分组的最大 id，但我似乎无法做到这一点。这是一个非常简化的表格和一些示例数据：
id title colour description
1 rico red blah1
2 rico blue blah2
3 rico yellow blah3
4 katia black blah4
5 katia white blah5
In this example with my code, I get 1 rico red blah1 when I want 3 rico yellow blah3.
在我的代码示例中，当我想要 3 个 rico Yellow blah3 时，我得到 1 个 rico red blah1。
Here is the code I am using:
这是我正在使用的代码：
SELECT pd_id, pd_title, pd_description, pd_colour,
pd_price,pd_large_image,pd_date,cat_sub_id_3,pd_new
FROM product
WHERE
cat_sub_id_1 = '".$cat_sub_id."'
AND cat_parent_id='".$cat_parent_id."'
GROUP BY pd_title
HAVING MAX(pd_id)
ORDER BY pd_id DESC
UPDATE: Thanks guys,
更新：谢谢各位，
I used alinoz's answer to come up with the following code which works :)
我使用 alinoz 的回答提出了以下有效的代码:)
SELECT
pd_id,pd_title,pd_description,pd_colour,pd_price,pd_large_image,pd_date,cat_sub_id_3,pd_new
FROM product
HERE cat_sub_id_1 = '".$cat_sub_id."' AND cat_parent_id='".$cat_parent_id."'
AND pd_id IN (
SELECT max(pd_id)
FROM product
WHERE cat_sub_id_1 = '".$cat_sub_id."' AND cat_parent_id='".$cat_parent_id."'
GROUP BY pd_title
)
GROUP BY pd_title
ORDER BY pd_id DESC
采纳答案by alinoz
try to add one more clause to your where with pd_id = (select max(pd_id) from ... )
尝试使用 pd_id = (select max(pd_id) from ... ) 在 where 中再添加一个子句
SELECT pd_id, pd_title, pd_description, pd_colour,
pd_price,pd_large_image,pd_date,cat_sub_id_3,pd_new
FROM product
WHERE
cat_sub_id_1 = '".$cat_sub_id."'
AND cat_parent_id='".$cat_parent_id."'
AND pd_id = (select max(pd_id) from product)
GROUP BY pd_title
This query is not optimal but it would do the job.
这个查询不是最优的，但它可以完成这项工作。
回答by Adriano Carneiro
Aaahhh, the good old greatest-n-per-group...
Aaahhh，好古老的每组最伟大的人......
select *
from YourTable yt
inner join(
select title, max(id) id
from YourTable
group by title
) ss on yt.id = ss.id and yt.title = ss.title
Of course, you should adapt this to your needs accordingly.
当然，您应该相应地根据自己的需要进行调整。
Also, I think this is a "must read": SQL Select only rows with Max Value on a Column
另外，我认为这是“必读”：SQL Select only rows with Max Value on a Column
回答by Salman A
I think this might work (not tested):
我认为这可能有效（未测试）：
SELECT * FROM that_table WHERE id IN (
SELECT MAX(id) FROM that_table GROUP BY title
) /* AND this = that */
回答by DnD
simply for one row by any (in this case 'id') max value
仅针对任意（在本例中为“id”）最大值的一行
SELECT * FROM `your_table` order by id desc limit 1
回答by ypercube??
Besides Adrian's and Salman's approach, there's alo this one which gives different results when there are ties, two or more rows with same (maximum) id. It will show only one row per title while the other query will show all of them (in your case that is probably irrelevant as you order by id, which I suppose is the Primary Key):
除了 Adrian 和 Salman 的方法之外，还有这个方法在有关系、两行或多行具有相同（最大）id 时给出不同的结果。它只会显示每个标题的一行，而另一个查询将显示所有这些（在您的情况下，这可能与您按 ID 订购无关，我认为这是主键）：
SELECT
t.*
FROM
TableX AS t
JOIN
( SELECT DISTINCT
title --- what you want to Group By
FROM TableX
) AS dt
ON t.PK = --- the Primary Key of the table
( SELECT tt.PK
FROM TableX AS tt
WHERE tt.title = dt.title
ORDER BY id ASC --- (or DESC) what you want to Order By
LIMIT 1
)
回答by user637646
I think, its not necessary to use subselects:
我认为，没有必要使用子选择：
select
SUBSTRING_INDEX(group_concat(testtab.ActionID order by testtab.ActionID DESC SEPARATOR '|'), '|', 1) as ActionIDs,
SUBSTRING_INDEX(group_concat(testtab.DiscountedPrice order by testtab.ActionID DESC SEPARATOR '|'), '|', 1) as DiscountedPrices
testtab.*
from testtab
where
testtab.StartDate <= 20160120
and testtab.EndDate > 20160120
and testtab.VariantID = 302304364
and testtab.DeleteStatus = 0
group by testtab.VariantID
order by testtab.VariantID, testtab.ActionID;
Its just a fast Example from my tests. Just by shure to order in the same way in the substring_indexes und use a good separator.
它只是我测试中的一个快速示例。只需在 substring_indexes 中以相同的方式排序并使用一个好的分隔符。
Have fun
玩得开心
回答by Naveen Babu
since you are already ordering, you can use row number =1 in where condition. see this link. and since your db is not known. I cant give a specific query but direct you in the direction
由于您已经在订购，因此您可以在 where 条件下使用行号 =1。看到这个链接。并且由于您的数据库未知。我不能给出具体的查询，但会指引你方向
or try sql topwhich will yield the same result, as order by is used
或尝试sql top这将产生相同的结果，因为使用了 order by
