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 a blank cell, here is C1, type this formula =MODE(A1:A13), and then press Enter key to get the most common number in the list. Tip: A1:A13 is the list you want to find most common number form, you can change it to meet your needs.
SELECT <column_name>, COUNT(<column_name>) AS `value_occurrence` FROM <my_table> GROUP BY <column_name> ORDER BY `value_occurrence` DESC LIMIT 1; Replace <column_name> and <my_table> . Increase 1 if you want to see the N most common values of the column. Save this answer.
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With