SQL根据一列中的最大值选择多列 [英] SQL Selecting multiple columns based on max value in one column
问题描述
好,因此我对其他解决方案没有任何帮助.所以这就是我想要做的. 我需要选择多列的行,其中一列的值为最大值.
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.
这是示例数据
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
我需要选择整行,其中orderfileid是每个唯一商品编号的最大值
I need to select the entire row where the orderfileid is the max for each unique item number
返回的数据集应该看起来像
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
我想我尝试了我能想到的select max(orderfileid)的每种组合
I think i tried every combination of select max(orderfileid) i can think of
将寻求任何帮助. 谢谢
Any help would be appriciated. thanks
推荐答案
您需要在子查询中找到MAX值,然后使用这些结果将其连接到主表中以检索列.
You need to find your MAX values in a subquery, then use those results to join to your main table to retrieve the columns.
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
这篇关于SQL根据一列中的最大值选择多列的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!