Skip to content
Advertisement

MAX COUNT BY GROUP BY Retrieve only max count records

I want to return only the max count record from the following grouping.

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 ;

This Returns:

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

Ideally it should return only :

AUDI                6
BMW                 6

Sort the manufname asc by

Tried this but returns the same:

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 ;

Advertisement

Answer

You can use dense_rank or rank window function. Also I have removed unnecessary aggregation in the top query.

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

You can use TOP with ties as following

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
User contributions licensed under: CC BY-SA
1 People found this is helpful
Advertisement