I've seen examples where the query orders by count and takes the top row, but in this case there can be multiple "most frequent" values, so I might want to return more than just a single result.
In this case I want to find the most frequently appearing last names in a users table, here's what I have so far:
select last_name from users group by last_name having max(count(*));
Unfortunately with this query I get an error that my max function is nested too deeply.
select
x.last_name,
x.name_count
from
(select
u.last_name,
count(*) as name_count,
rank() over (order by count(*) desc) as rank
from
users u
group by
u.last_name) x
where
x.rank = 1
Use the analytical function rank. It will assign a numbering based on the order of count(*) desc. If two names got the same count, they get the same rank, and the next number is skipped (so you might get rows having ranks 1, 1 and 3). dense_rank is an alternative which doesn't skip the next number if two rows got the same rank, (so you'd get 1, 1, 2), but if you want only the rows with rank 1, there is not much of a difference.
If you want only one row, you'd want each row to have a different number. In that case, use row_number. Apart from this small-but-important difference, these functions are similar and can be used in the same way.
select name
from
(select name, count(1)
from table
group by name
order by count(1) desc) a
where rownum = 1
