How to properly filter columns after applying aggregate function

Question

I find that I need to apply an aggregate a lot, but I don't want to show the aggregated value as well. So the best way to do this was to just nest the query to filter it out (like below) but there has to be a better way, how?

select state
from(
 select state, max(cnt)
 from (
 select state, count(*) as cnt
 from customer
 group by state) as t
) as r;

Here I just want to show state without the max value.

• ypercubeᵀᴹ: You want the state with the max count? What is there are two (or more) with the same max count?
• shinzou: @ypercubeTM it will take one of them I guess? It doesn't matter.
• shinzou: I said it (the result) doesn't matter, I guessed the behavior. @ypercubeTM

Answer 1

I think you can achieve this result without so many subqueries:

Select State
 From Customer
 Group By State
 Order By Count(*) Desc
 Limit 1;

• shinzou: Nice to know it's possible to limit the query.