select a, max(count(b)) from... NOT WORKING! 1

[kev510]

hello everyone,

I have been trying to figure this out for the last 45 minutes... Finally, I gave in and decided to post here. To summarize, I have a table with 2 fields. I need to pull the maximum count of field B, with its corresponding field A value. I tried the below -

select max(count(b)), a from TABLE group b, a

but i keep getting error "not a single-group group function".
Thanks in advance for your help!

 

[kev510]

EDIT** i used "GROUP BY" not "GROUP"..
so that's not an issue.

 

[gmmastros]

I assume you want just 1 record whit A and the Count(b) where Count(b) is the max count for all the a's ???

In SQL Server, I would write this as...

Select [!]Top 1[/!] count(b), a 
from   TABLE
group By a
[!]Order By Count(b) DESC[/!]

The syntax is likely to be different for your DB, but the concept should be the same. Order the output by Count(b) Descending, and return the Top 1.

Hope this helps.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[kev510]

I apologize.
There is one column in the table. I want to get the maximum count of column A, and pull what value the count is for. Here's an example -

Column A
1
2
2
2
3

I want to see

MAX(Count(A)) Value
3 2

Thanks again!!

 

[r937]

first of all, what you have isn't a relational table -- it cannot be, because every table has a primary key and a primary key doesn't allow duplicates

you may think that's splitting hairs, but after all, it was you who posted in the ANSI SQL forum instead of the forum for your particular database management system, which might allow you to create such a table anyway

select columnA
  from ( select columnA
              , count(columnA) as daCount
           from daTable
         group 
             by columnA ) as t1
 where daCount = 
       ( select max(daCount)
	         from ( select columnA
                       , count(columnA) as daCount
                    from daTable
                  group 
                      by columnA ) as t2 )

r937.com | rudy.ca

 

[elsenorjose]

Rudy,

If I recall correctly, the ANSI SQL standard doesn't require keys to be defined and allows key fields to be NULL...you COULD say that there's nothing wrong with posting the question here

But of course you're correct in stating that the RELATIONAL model doesn't allow dupes and does require keys.

Which is why Fabian Pascal argues that there are NO RDBMSs, only SQL DBMSs

 

[JarlH]

ANSI SQL allows tables without a key, but a relational table requires a key. (ANSI SQL isn't truly relational.)

And in most cases a table needs a key!