Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

how to select the most frequently appearing values? [duplicate]

Tags:

sql

oracle

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.

like image 303
in His Steps Avatar asked Oct 09 '11 19:10

in His Steps


People also ask

How do I find the most repeated values in Excel?

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.

How do I get the most repeated values in SQL?

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.


1 Answers

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.

like image 81
GolezTrol Avatar answered Oct 14 '22 13:10

GolezTrol