wood burning stoves 2.0*
The moose likes JDBC and the fly likes SQL for the top 2 most recurring values in a column Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Soft Skills this week in the Jobs Discussion forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "SQL for the top 2 most recurring values in a column" Watch "SQL for the top 2 most recurring values in a column" New topic
Author

SQL for the top 2 most recurring values in a column

Sonny Gill
Ranch Hand

Joined: Feb 02, 2002
Posts: 1211

Hi All,

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?

thanks for your time
Sonny
arnel nicolas
Ranch Hand

Joined: Dec 16, 2003
Posts: 149
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 ]
Sonny Gill
Ranch Hand

Joined: Feb 02, 2002
Posts: 1211

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.

Thanks again.
Sonny
Sonny Gill
Ranch Hand

Joined: Feb 02, 2002
Posts: 1211

AND YES!!

This seems to be working like a charm

SELECT ROWNUM, x_code FROM
(
SELECT aCol, COUNT(aCol) AS num FROM aTable
GROUP BY aCol order by num DESC
) aCols
WHERE ROWNUM <= 2


Thanks again Arnel

Sonny
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: SQL for the top 2 most recurring values in a column