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
任何帮助都会得到满足。谢谢
6 个解决方案
#1
14
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
#2
2
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
#3
0
Try
尝试
SELECT * FROM `TABLE` WHERE orderfileid=(select max(orderfileid) from TABLE)
#4
0
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
删除行:没有单个成员具有超过x个记录
this is something similar to doing rank over in Oracle. my previous post was for oracle. my bad..
这与在Oracle中排名相似。我以前的帖子是为了oracle。我的错..
#5
0
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
#6
-2
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,仓库;
#1
14
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
#2
2
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
#3
0
Try
尝试
SELECT * FROM `TABLE` WHERE orderfileid=(select max(orderfileid) from TABLE)
#4
0
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
删除行:没有单个成员具有超过x个记录
this is something similar to doing rank over in Oracle. my previous post was for oracle. my bad..
这与在Oracle中排名相似。我以前的帖子是为了oracle。我的错..
#5
0
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
#6
-2
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,仓库;