Using MAX to compute MAX value in a subquery column

Question

What I am trying to do: I have a table, "band_style" with schema (band_id, style). One band_id may occur multiple times, listed with different styles. I want ALL rows of band_id, NUM (where NUM is the number of different styles a band has) for the band ids with the SECOND MOST number of styles.

I have spent hours on this query- almost nothing seems to be working.

This is how far I got. The table (data) successfully computes all bands with styles less than the maximum value of band styles. Now, I need ALL rows that have the Max NUM for the resulting table. This will give me bands with the second most number of styles.

However, this final result seems to be ignoring the MAX function and just returning the table (data) as is. Can someone please provide some insight/working method? I have over 20 attempts of this query with this being the closest.

Using SQL*PLUS on Oracle

WITH data AS (
SELECT band_id, COUNT(*) AS NUM FROM band_style GROUP BY band_id HAVING COUNT(*) < 
(SELECT MAX(c) FROM
(SELECT COUNT(band_id) AS c
FROM band_style
GROUP BY band_id)))
SELECT data.band_id, data.NUM FROM data
INNER JOIN ( SELECT band_id m, MAX(NUM) n 
             FROM data GROUP BY band_id
           ) t
   ON t.m = data.band_id 
     AND t.n = data.NUM;

Answer 1

Something like this... based on a Comment under your post, you are looking for DENSE_RANK()

select band_id
from   ( select   band_id, dense_rank() over (order by count(style) desc) as drk
         from     band_style
         group by band_id
       )
where  drk = 2;