mysql - SQL选择两列组合的最高行

Question

我需要有关 MySQL 5.5 查询的帮助

在按 location_id、count_number、try_number 对行进行排序后，我尝试只为 2 列(tan、location_id)的每个组合选择 1 行。所以基本上我想要一个只返回下图中的黄色行的查询。每个 tan/location_id 组合仅包含该组内具有最高“count_number”和“try_number”的 1 条记录。

这是我当前的查询。

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;

Answer 1

我认为这符合你的要求:

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
                                   );