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