How to get the most frequent value in a column?

Question

I have a table with column 'Price' and would need to get the most frequent value. What would be the eeasiest way?

score 2 · Answer 1 · answered Mar 28 '13 at 09:28

One option would be something like

SELECT price
  FROM (SELECT price, rank() over (order by cnt desc) rnk
          FROM (SELECT price, count(*) cnt
                  FROM your_table
                 GROUP BY price))
 WHERE rnk = 1

If there are two (or more) prices that occur equally as often, both will be returned by this query. If you want to guarantee a single row, you'll need to tell us how you want to handle ties.

score 0 · Answer 2 · answered Mar 28 '13 at 09:25

0

My algorithm is as follows:

Step one: make distinct selection as a collection;
Step two: foreach item in distinct collection count the items found in the original collection as diffcollection;
Step three: select max from diffcollection.

answered Mar 28 '13 at 09:25

Andries

1,517
10
29

1

This can be done with a single SQL statement... – a_horse_with_no_name Mar 28 '13 at 09:27
But, does the algorithm differ? – Andries Mar 28 '13 at 09:33
No, but it's less efficient. – a_horse_with_no_name Mar 28 '13 at 09:38

How to get the most frequent value in a column?

2 Answers2