sql选择两列组合中最高的一行

wvt8vs2t 于 2021-06-20 发布在 Mysql

关注(0)|答案(2)|浏览(359)

我需要帮助查询MySQL5.5
在按location\u id、count\u number和try\u number对行进行排序之后，我尝试对每两列的组合（tan、location\u id）只选择一行。所以基本上我想要一个只返回下图中黄色行的查询。每个tan/location\u id组合只有一条记录，并且在该组中具有最高的“count\u number”和“try\u number”。

这是我目前的查询。

select tan, quantity, count_number, try_number, location_id 
from inventario_inventoryregistry
where tan = '53-100554-01'
order by location_id desc, count_number desc, try_number desc;

sql mysql groupwise-maximum

来源：https://stackoverflow.com/questions/50979683/sql-to-select-highest-row-of-a-combination-of-two-columns

2条答案

按热度按时间

tjrkku2a1#

我想这正是你想要的：

select iir.*
from inventario_inventoryregistry iir
where (count_number, try_number) = (select iir2.count_number, iir2.try_number
                                    from inventario_inventoryregistry iir2
                                    where iir2.tan = iir.tan and iir2.location_id = iir.location_id
                                    order by iir2.count_number desc, iir2.try_number desc
                                    limit 1
                                   );

赞(0）回复(0）举报 2021-06-20

slhcrj9b2#

select tan, quantity, count_number, try_number, location_id
from
(select tan, quantity, count_number, try_number, location_id, row_number() over (partition by location_id, tan_id order by location_id desc, count_number desc, try_number desc) rw_nm
from inventario_inventoryregistry
where tan = '53-100554-01')
where rw_nm = 1;

赞(0）回复(0）举报 2021-06-20

我来回答

sql选择两列组合中最高的一行

2条答案

相关问题

热门标签

最新问答