Wednesday, November 16, 2005

How to create a SQL query for selecting most frequently occurring values in a column

To select values for a column with the highest recurrence

SELECT COL1 FROM TABLE1 GROUP BY COL1 HAVING COUNT(*) >= ALL(SELECT COUNT(*) FROM TABLE1 GROUP BY COL1);

For DB2 the following query may be a bit more optimized

SELECT COL1 FROM TABLE1 GROUP BY COL1 HAVING COUNT(*) = (SELECT COUNT(*) FROM TABLE1 GROUP BY COL1 ORDER BY COUNT(*) DESC FETCH FIRST ROW ONLY);