Win a copy of Java Mock Exams (software) this week in the Programmer Certification (OCPJP) forum!
  • Post Reply Bookmark Topic Watch Topic
  • New Topic

SQL questions

 
Elizabeth King
Ranch Hand
Posts: 191
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 1082
Java Oracle Spring
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
 
Michael Matola
whippersnapper
Ranch Hand
Posts: 1826
5
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 191
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 1826
10
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
That would be just the basic query, without the WHERE clause



Cheers,
evnafets
 
What are you doing? You are supposed to be reading this tiny ad!
the new thread boost feature brings a LOT of attention to your favorite threads
https://coderanch.com/t/674455/Thread-Boost-feature
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
Boost this thread!