按组列出的最大计数仅检索最大计数记录

htzpubme 于 2021-08-09 发布在 Java

关注(0)|答案(1)|浏览(414)

我只想返回以下分组中的max count记录。

select veh_manufname,  count(*) as "Total Count"  
from offence o 
join demerit d on o.dem_code = d.dem_code 
join vehicle v on v.veh_vin = o.veh_vin 
group by veh_manufname
having sum(d.dem_points)>=2 
order by "Total count" desc, veh_manufname ;

这将返回：

Veh_manufname   Total Count
 BMW                 6
 AUDI                6
 TATA                5
 ..                 ..

理想情况下，它应该只返回：

AUDI                6
BMW                 6

将manufname asc按
尝试此操作，但返回相同的结果：

select veh_manufname, max(TOTAL_COUNT)
FROM (
select veh_manufname,  count(*) as "TOTAL_COUNT"  from offence o join demerit d on o.dem_code = d.dem_code join vehicle v 
on v.veh_vin = o.veh_vin group by veh_manufname
having sum(d.dem_points)>=2 )
GROUP BY veh_manufname
order by max(TOTAL_COUNT) desc, veh_manufname ;

sql Join subquery

来源：https://stackoverflow.com/questions/62275069/max-count-by-group-by-retrieve-only-max-count-records

1条答案

按热度按时间

aelbi1ox1#

你可以用 dense_rank 或者 rank 窗口功能。我还删除了顶部查询中不必要的聚合。

select 
    veh_manufname, 
    TOTAL_COUNT
FROM (
    select 
        veh_manufname,  
        count(*) as TOTAL_COUNT ,
        dense_rank() over (order by count(*) desc) as rnk 
    from offence o 
    join demerit d 
    on o.dem_code = d.dem_code 
    join vehicle v 
    on v.veh_vin = o.veh_vin 
    group by 
        veh_manufname
    having sum(d.dem_points)>=2 
) t
where rnk = 1

你可以用 TOP with ties 如下所示

select 
    veh_manufname, 
    TOTAL_COUNT
FROM (
    select 
        veh_manufname,  
        count(*) as TOTAL_COUNT 
    from offence o 
    join demerit d 
    on o.dem_code = d.dem_code 
    join vehicle v 
    on v.veh_vin = o.veh_vin 
    group by 
        veh_manufname
    having sum(d.dem_points)>=2 
) t
order by 
    TOTAL_COUNT desc
fetch first 1 row with ties

赞(0）回复(0）举报 2021-08-09

我来回答

按组列出的最大计数仅检索最大计数记录

1条答案

相关问题

热门标签

最新问答