how to select the most frequently appearing values? [duplicate]
select x.last_name, x.name_countfrom (select u.last_name, count(*) as name_count, rank() over (order by count(*) desc) as rank from users u group by u.last_name) xwhere x.rank = 1Use 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.