Getting all the driver license numbers who have equal to the maximum amount of offences for each demerit code

Question

I'm doing this for a uni assignment, the task is to basically get all the driver license numbers who have equal to the maximum amount of offences for each demerit code.

So there's an offences table, demerits table, and drivers table, and the query should return a list of all the drivers licenses which have gotten the most tickets for each demerit category, and if there's equal maximums, return all the drivers license who got that maximum for that demerit category.

I got it working with a super ugly nested subquery setup like this:

select 
    sp.dem_code as "Demerit Code", 
    dem.dem_description as "Demerit Description", 
    sp2.lic_no as "License No.",
    d.lic_fname || ' ' || d.lic_lname as "Driver Fullname",
    sp."Total Times Booked"
from (
    select
        dem_code,
        max(o.num) as "Total Times Booked"
    from (
        select 
            dem_code, 
            count(lic_no) as num, 
            lic_no 
        from offence 
        group by dem_code, lic_no 
        order by dem_code asc
    ) o
    group by dem_code
    order by dem_code asc
) sp
join (
    select 
        dem_code, 
        count(lic_no) as num, 
        lic_no 
    from offence 
    group by dem_code, lic_no 
    order by dem_code asc
) sp2 on sp."Total Times Booked" = sp2.num and sp.dem_code = sp2.dem_code
join driver d on sp2.lic_no = d.lic_no
join demerit dem on dem.dem_code = sp2.dem_code
order by sp.dem_code asc, sp2.lic_no asc
;

But I can't figure out why something something like:

select
    dem_code,
    max(num),
    lic_no
from (
select dem_code, count(lic_no) as num, lic_no from offence group by dem_code, lic_no order by dem_code asc
)
group by dem_code
;

or

select
    dem_code,
    max(num),
    lic_no
from (
select dem_code, count(lic_no) as num, lic_no from offence group by dem_code, lic_no
)
group by dem_code
having num = max(num)
order by dem_code asc
;

wouldn't work?

It basically just comes down to grabbing all the license numbers that equal the max value for that group of demerit codes.

Answer 1

Why pl/sql when you can do it with SQL :D

Assuming table offence is ...

(
dem_code varchar2(100),
lic_no varchar2(100),
lic_fname varchar2(100),
... ticket_no varchar2(1),
... ticket_dtls varchar2(1000),
...
);

There are two levels of SQL Analytical Window functions needed (as per me).

1. Calculate the total per person per demerit code.
2. Calc the max infringements per demerit code or max (per person demerit totals) across all people for same demerit code.

We cant use Window functions in the where clause so need Q1 and Q2 as two successive layers of calculations (pipeline of input -> Q1 -> Q2 -> Resultset).

Query:

With Q1 as (
select 
   dem_code, 
   lic_no, 
   lic_fname,
   -- count per person per demerit code
   count(1) over (partition by dem_code, lic_fname) as fname_dc_num
from offence
),
Q2 as (
select 
   dem_code, 
   lic_no, 
   lic_fname,
   fname_dc_num,
   -- max count by any person for demerit code
   max(fname_dc_num) keep (DENSE_RANK FIRST ORDER BY fname_dc_num desc) over (partition by dem_code) as max_dc_num
from Q1
)
select *
from Q2
where fname_dc_num = max_dc_num
order by dem_code asc, lic_no, lic_fname
;