I want to return only the max count record from the following grouping.
x
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