Win a copy of Programmer's Guide to Java SE 8 Oracle Certified Associate (OCA) this week in the OCAJP forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

SQL for the top 2 most recurring values in a column

 
Sonny Gill
Ranch Hand
Posts: 1211
IntelliJ IDE Mac
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 149
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 1211
IntelliJ IDE Mac
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 1211
IntelliJ IDE Mac
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic