• 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: 1817
4
  • 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: 1692
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
 
With a little knowledge, a cast iron skillet is non-stick and lasts a lifetime.
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic