MySQL SQL根据一列中的最大值选择多列
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6860746/
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 Selecting multiple columns based on max value in one column
提问by larry hartman
OK so I have looked theough the other solutions an no help. So here is what I am trying to do. I need to select the row with multiple columns where the value in one column is the max value.
好的，所以我已经看了其他解决方案，但没有帮助。所以这就是我想要做的。我需要选择多列的行，其中一列中的值是最大值。
here is sample data
这是示例数据
orderfileid item number item cost warehouse
1 1234 3.45 ATL
1 2345 1.67 DFW
3 2345 2.45 NYY
3 678 2.4 ORD
2 1234 1.67 DFW
I need to select the entire row where the orderfileid is the max for each unique item number
我需要选择整个行，其中 orderfileid 是每个唯一项目编号的最大值
the returned dataset should look like
返回的数据集应该看起来像
orderfileid item number item cost warehouse
2 1234 1.67 DFW
3 2345 2.45 NYY
3 6789 2.4 ORD
I think i tried every combination of select max(orderfileid) i can think of
我想我尝试了所有我能想到的 select max(orderfileid) 组合
Any help would be appriciated. thanks
任何帮助将被appriciated。谢谢
回答by Joe Stefanelli
You need to find your MAX values in a subquery, then use those results to join to your main table to retrieve the columns.
您需要在子查询中找到您的 MAX 值，然后使用这些结果连接到您的主表以检索列。
SELECT t.OrderFileId, t.ItemNumber, t.ItemCost, t.Warehouse
FROM YourTable t
INNER JOIN (SELECT ItemNumber, MAX(OrderFileId) AS MaxOrderId
FROM YourTable
GROUP BY ItemNumber) q
ON t.ItemNumber = q.ItemNumber
AND t.OrderFileId = q.MaxOrderId
回答by Derek Kromm
select
t.*
from
table t
inner join (
select itemnumber, max(orderfileid) maxof
from table
group by itemnumber
) m on t.itemnumber = m.itemnumber
and t.orderfileid = m.maxof
回答by user2593417
I wouldn't even use Max. Just combine GROUP BY and ORDER BY
我什至不会使用Max。只需结合 GROUP BY 和 ORDER BY
SELECT * FROM orders GROUP BY item_number ORDER BY orderfileid DESC
then for minimum just change to ASC
那么最低限度只需更改为 ASC
回答by user2593417
you can refer to a similar problem on how to group things using partitioning and picking one per partition in mysql
您可以参考一个类似的问题，了解如何使用分区对事物进行分组并在 mysql 中为每个分区选择一个
Deleting Rows: No Single Member Has More Than x Records
this is something similar to doing rank over in Oracle. my previous post was for oracle. my bad..
这类似于在 Oracle 中进行排名。我之前的帖子是针对 Oracle 的。我的错..
回答by Ricardo
Try
尝试
SELECT * FROM `TABLE` WHERE orderfileid=(select max(orderfileid) from TABLE)
回答by Vidyanand
I think what you are looking for is the "Having" clause. Take a look at this.
我认为您正在寻找的是“拥有”条款。看看这个。
select orderfileid, max(itemnumber), itemcost, warehouse from MyTable group by orderfileid having max(itemnumber) ;
通过具有 max(itemnumber) 的 orderfileid 从 MyTable 组中选择 orderfileid, max(itemnumber), itemcost, 仓库；
