• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

SQL for the top 2 most recurring values in a column

 
Ranch Hand
Posts: 1211
Mac IntelliJ IDE
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
 
Ranch Hand
Posts: 149
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
Mac IntelliJ IDE
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
Mac IntelliJ IDE
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
 
Dinner will be steamed monkey heads with a side of tiny ads.
a bit of art, as a gift, the permaculture playing cards
https://gardener-gift.com
reply
    Bookmark Topic Watch Topic
  • New Topic