Phi Zeroth Phi Zeroth - 1 month ago 5x
SQL Question

Top item for each top 5 group by record count (Access SQL)

I have a single large table

with thousands of records of denied claims. Each one comes from a supplier and has a denial reason and a status. Only the
is unique.

id | denial | supplier | status
1 | Duplicate claim | ACME | Adjusted
2 | Not authorized | Umbrella Corp | Adjusted
3 | Not authorized | Stark Ind. | Adjusted
4 | Rec'd after due | ACME | Override
5 | Duplicate claim | Stark Ind. | Adjusted
... etc

What I need is the top 5 denial reasons for "Adjusted" status claims by record count, and the top provider by record count for each of those denial reasons.

denial | cnt_denial | top_supplier | cnt_top_supplier
Not authorized | 917 | Stark Ind. | 351
Duplicate claim | 685 | Stark Ind. | 195
Not in contract | 525 | ACME | 216
Rec'd after due | 512 | Umbrella Corp | 500
Explosions | 349 | ACME | 231

I've tried lots of ways, mostly trying to adapt other somewhat similar solutions, but I'm overreaching my meager SQL knowledge here and getting frustrated. Several solutions I've tried do not work in MS Access (2010). I created two queries as a start, but I'm having trouble joining them in the way that I need.

This query returns exactly the denial reason data that I need:

SELECT TOP 5 denial, Count(*) AS cnt_denial
FROM Audit
GROUP BY status, denial
HAVING status="Adjusted"

And this query has a count of every denial/supplier grouping in the table. I'm not sure how to get the top supplier for each denial reason, which seems like it should be simple but I'm having trouble. EDIT: This is my primary problem. If I can just get the top single supplier for each denial reason I can figure out the join. I've tried using MAX but haven't succeeded yet. /EDIT

SELECT denial, supplier, Count(*) AS cnt_supplier
FROM Audit
GROUP BY denial, supplier, status
HAVING status="Adjusted"

I need a single query since I'll just be passing this from Excel via ADO. Any help would be hugely appreciated.


I don't know if this is perfectly valid Access SQL. It shouldn't require much tweaking if it isn't:

    ds.supplier as top_supplier,
    ds.cnt_supplier as cnt_top_supplier
        select top 5 denial, count(*) as cnt_denial
        from Audit
        where status = 'Adjusted'
        group by denial
        order by count(*) desc
    ) d
    inner join
        select denial, supplier, count(*) as cnt_supplier
        from Audit
        where status = 'Adjusted'
        group by denial, supplier
    ) ds
        on ds.denial = d.denial
where not exists (
    select 1
    from Audit as a2
    where a2.status = 'Adjusted'
        and a2.denial = ds.denial and a2.supplier <> ds.supplier
    group by a2.supplier
    having count(*) > ds.cnt_supplier
        /* or count(*) = ds.cnt_supplier and a2.supplier < ds.supplier -- tiebreaker */
order by d.cnt_denial desc, ds.supplier

By using the top 5 it's grabbing only five rows regardless of ties. Then the top 1 per group is handled with a subquery and in that case you would include ties and potentially retrieve more than five rows in the final result. It's not too difficult to include ties at the denial level if you need that or to break ties and exclude them in the supplier ranking.

EDIT: I threw together some test data and the tie-breaking appears to work on SQL Server.