How could I go about writing a SQL statement to find top 2 values that recurr most in a column?

Say a table has one column, and that column can have, say one of the 5 values, A, B, C, D or E repeated any number of time. How can I find out the two values with the most number or occurances in that column?

The actual problem is a bit more complex, but this is what it boils down to. Of course I could get all values and iterate through those and count the number of times each one occurs, but is there a simpler or faster solution?

I don't know if this works with other database. :roll:

I solve this using a nested select statement.You can COUNT the rows by first GROUPING them then issue another select statement to retrieve the 2 TOP rows.

Other than that you can solve it in your Java code. [ July 28, 2004: Message edited by: arnel nicolas ]

Thanks mate, I will try that out. I might have to use another nested select to use Oracle rownum. On the other hand if the total number of different possible values only slightly higher than the top N values I need, I might try to do it in Java.