SQL根据一列中的最大值选择多个列

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

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

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

Try

尝试

SELECT * FROM `TABLE` WHERE orderfileid=(select max(orderfileid) from TABLE)

#4

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

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