wood burning stoves 2.0*
The moose likes JDBC and the fly likes SQL questions Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of OCA/OCP Java SE 7 Programmer I & II Study Guide this week in the OCPJP forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "SQL questions" Watch "SQL questions" New topic
Author

SQL questions

Elizabeth King
Ranch Hand

Joined: Jul 11, 2002
Posts: 191
I have a column of data in an Oracle 9i DB, e.g.,

Values
------
1
1
1
22
2
asad
aaaa
aaaa
cc
cc
bbbb
bbbb
bbbb

Could anyone help me to create a query that returns most occuring values

1
bbbb

and also least occuring data

22
2
asad
Shailesh Chandra
Ranch Hand

Joined: Aug 13, 2004
Posts: 1081

On the first place I will make a query like



similarly for least occurrence




I don't have database with me right now but the query should work

Shailesh


Gravitation cannot be held responsible for people falling in love ~ Albert Einstein
Michael Matola
whippersnapper
Ranch Hand

Joined: Mar 25, 2001
Posts: 1751
    
    2
Hey, I did this here once before!

Here you go:

select field1
from table1
group by field1
having count(*) = (
select max( mycount )
from ( select count(*) mycount
from table1
group by field1 )
)

Let me know if you want an explanation.
Elizabeth King
Ranch Hand

Joined: Jul 11, 2002
Posts: 191
They all work. Thanks. How do I list all the values with their
occuring count:

Values Counts
1 3
bbbb 3
aaaa 2
cc 2
2 1
22 1
asad 1
Stefan Evans
Bartender

Joined: Jul 06, 2005
Posts: 1018
That would be just the basic query, without the WHERE clause



Cheers,
evnafets
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: SQL questions