CODEWITHSUNDEEP
sqloracle-database
sania247
sania247

Reputation: 33

How does finding max by using >=all work?

I want to find the max count from the table and know how it is done using all. But I just don't understand how it works. Like when you say count()>=all(select count() rest of the query) then that just means that if the count is not greater than all of them then it can be equal to them? like how is that possible when there is more than one value in the subquery?

Here is my code

select 
    m.name,
    count(*) 
from 
    manufacturer m 
    inner join products p on m.code=p.manufacturer
group by m.name,m.code
having count(*)<=all(
    select count(*) 
    from manufacturer m
    inner join products p on m.code=p.manufacturer 
    group by m.code
);

Upvotes: 1

Views: 107

Answers (1)

GMB
GMB

Reputation: 222602

From the documentation :

The ALL comparison condition is used to compare a value to a list or subquery. It must be preceded by =, !=, >, <, <=, >= and followed by a list or subquery.

When the ALL condition is followed by a list, the optimizer expands the initial condition to all elements of the list and strings them together with AND operators

In your use case you would want :

... having count(*) >= all(select count(*) ...

Instead of :

... having count(*) <= all(select count(*) ...

This will return the item whose count is equal to the maximum possible value (or more than one item if there are top ties).

However there are simpler options to obtain the result you want. Just order the results of your query and use ROWNUM to select the top row, like :

select * from (
    select m.name,count(*) cnt
    from 
        manufacturer m 
        inner join products p 
            on m.code=p.manufacturer 
    group by m.name,m.code
    order by 2 desc
) where rownum = 1

Upvotes: 1

Related Questions