Home » SQL & PL/SQL » SQL & PL/SQL » Max(count(xxx))
Max(count(xxx)) [message #189926] Mon, 28 August 2006 10:32 Go to next message
ssanjinika
Messages: 1
Registered: August 2006
Junior Member
Hi Everyone,
I have a question with count. I know we can have a select statement which is something like
select max(count(xxx)) from XXX.
I need a SQL statement which does exactly this but. Can someone please help me with it.
Details are below

Table1
Id CC
3322 cost1
3322 cost1
3322 cost1
3322 cost1
3322 cost1
3322 cost2
3322 cost2
3323 cost1
3323 cost1
3323 cost2
3323 cost2
3323 cost2
3323 cost2
I need an SQL statement which will return cost1 for 3322 and cost2 for 3323
in other words I need the max of count(id,cc) from the table. I cant use a cursor or insert the count into table.Is it possible to do this in one single SQL statement using subqueries or joins?Urgently needed .Please help!
Re: Max(count(xxx)) [message #189979 is a reply to message #189926] Mon, 28 August 2006 18:22 Go to previous messageGo to next message
Nirmala
Messages: 43
Registered: October 2004
Member
If i understand you correctly this is what you want.

select max(id) , cc from table1 group by cc;
Re: Max(count(xxx)) [message #189981 is a reply to message #189926] Mon, 28 August 2006 19:21 Go to previous messageGo to next message
Todd Barry
Messages: 4819
Registered: August 2001
Senior Member
One way:

sql>select id, cc
  2    from table1 a
  3   group by id, cc
  4   having count(*) = (select max(count(*))
  5                        from table1 b
  6                       where b.id = a.id
  7                       group by b.id, b.cc);

       ID CC
--------- -----
     3322 cost1
     3323 cost2

2 rows selected.
Re: Max(count(xxx)) [message #190075 is a reply to message #189981] Tue, 29 August 2006 04:11 Go to previous message
zozogirl
Messages: 77
Registered: November 2005
Location: Seoul, Korea
Member

And, another way:

SELECT ID
     , cc
  FROM (SELECT   ID
               , cc
               , COUNT (*) cnt
               , MAX (COUNT (*)) OVER (PARTITION BY cc) max_cnt
            FROM table1
        GROUP BY ID
               , cc)
 WHERE cnt = max_cnt

Query Your Dream & Future at SoQooL
http://www.soqool.com
Previous Topic: How to detect if a field is empty?
Next Topic: Select from List of numbers...interesting error!
Goto Forum:
  


Current Time: Sat Dec 10 13:01:52 CST 2016

Total time taken to generate the page: 0.17540 seconds