sql select most frequent value in column 1

sql select most frequent value in column

SELECT col, COUNT(col) AS value_occurrence
FROM m_table GROUP BY col ORDER BY value_occurrence DESC LIMIT 1;

-- Oracle <= 11g
SELECT * FROM (SELECT col, COUNT(col) AS value_occurrence
FROM m_table GROUP BY col ORDER BY value_occurrence DESC)
WHERE rownum = 1;

Here is what the above code is Doing:
1. We're selecting the column and the count of the column.
2. We're grouping by the column.
3. We're ordering by the count of the column in descending order.
4. We're limiting the result to 1 row.

Similar Posts