how to count first then find max??

[Guest_imported]

hi

I have a table customer which stores customer's info.
I have to find which state has the most customers.
I have to print out the state and the number of customers in that state only.

my query is:

select state, max(total) from customer,
(select count(state) as total from customer group by state)

this gives me a syntax error. In fact, I have tried many different ways to do it but they woulnd't work.
Can anyone help me out here? Thanx in advance.

 

[yearnings]

I think it can get the result you want
Just like this:


Select Top 1 State, Total
From (Select State, Count(*) as Total
From Customer
Group By State) State_Customer_Num
Order By Total Desc

 

[Guest_imported]

the output is sorted, but it prints out all of the states with the number of customers. I think "TOP 1" is not executed. Is it because of the version? I m using SQLanywhere 7.0.1

 

[Bikho]

What about this example

select top 1 state,count(state)
from customer
group by state
order by 2 desc

 

[yearnings]

Hi mhuma,

I think another question, If there are two or more states have equal number of customers, using "TOP 1" will not
get all the results, another query like this:

Select StateId, Total
From (Select StateId, Count(StateId) As Total
From Customer
Group By StateId) State_Customer_Num
Where Total=(Select Max(Total)
From (Select StateId, Count(StateId) As Total
From Customer
Group By StateId) State_Customer_Num)

It can get all the exact results, but I think the better
way is using temp table.

btw: I am using MSSQL70.

Hope this helpful

GL