CODEWITHSUNDEEP
sql
CodeGuy
CodeGuy

Reputation: 28907

Using aggregate function max in SQL

I have the following query

select 
    sub.W
from 
    (select 
        W, count(W) as N
     from L
     group by W) sub
where 
     sub.N >= max(sub.N)

and I get this error:

Error code 1111, SQL state HY000: Invalid use of group function
Line 1, column 1

What is wrong?

Upvotes: 0

Views: 177

Answers (2)

Robert
Robert

Reputation: 25753

Try:

select sub.W    
from 
(
       select W, count(W) as N
       from L
       group by W
) sub    
where sub.N >= (select max(N)
                from (
                       select W, count(W) as N
                       from L
                       group by W
                      ) sub2)

Upvotes: 0

Taryn
Taryn

Reputation: 247690

Have you tried this:

select sub.W
from 
(
       select W, count(W) as N
       from L
       group by W
) sub
where n >= (select max(N)
            from
            (
              select count(W) as N
              from L
              group by W
            ) x)

See SQL Fiddle with Demo

Upvotes: 3

Related Questions