0

I have this tables, and i would like to retrieve the customer who placed the highest number of orders.

i wrote this query:

select * from customers where cust_id=(
select cust_id from orders
group by cust_id
having count(*)>=all(select count(cust_id)
                     from orders
                     group by cust_id))

I know that maybe there is a better way to do it, but I was surprised to find that 'ALL' works with '> ='

In my understanding, 'ALL' checks whether the current row is greater or less than all rows in the sub-query, but I never thought it was possible to use it with '='.

If i use it with '=' or '>' the query doesn't return me any rows as i was expecting.

But if i use them together '>=' the query gives me the correct results.

Isn't it strange?

Schema Tables

Anyway, at the end i wrote this query:

SELECT *
FROM Orders, customers
WHERE orders.cust_id=customers.cust_id
and orders.cust_id IN
            (SELECT TOP (1) o.cust_id
            FROM Orders AS O
            GROUP BY O.cust_id
            ORDER BY COUNT(*) DESC);

Do you have a better or more elegant solution?

And isn't it strange the use of '>=' in 'ALL' clause?

Thank you.

7
  • 1
    Your queries are inconsistent. Do you want to return columns from customers only or both from customers and orders? Nov 17 '16 at 19:28
  • 1
    Plus the last query is not correct. If there is a tie in the highest number of orders, only one customer will be returned, not all of them. Nov 17 '16 at 19:28
  • i want to see the customers, and if it's possible, even the orders. how would you suggest to write this query?
    – andrea
    Nov 17 '16 at 19:31
  • 3
    >= is just another way to say not less than. Why would > be valid but not NOT <? Nov 17 '16 at 19:41
  • 1
    By the way = ALL (...) doesn't (and wouldn't) return anything because not all counts are equal. If they were (all counts equal), then it would return all of them. WHERE something = ALL (...) means that the something must be equal to all values in the subquery for the condition to be true. Nov 17 '16 at 19:56
6

Various ways to write. Your query, slightly rewritten to use JOIN and corrected to return all customers in case of ties:

select c.*, o.*
from customers as c
    join orders as o
      on o.cust_id = c.cust_id
where c.cust_id in 
      ( select cust_id 
        from orders
        group by cust_id
        having count(*) >=  
               all ( select count(*)
                     from orders
                     group by cust_id
                   )
      ) ;

Similar, without using >= ALL:

select c.*, o.*
from customers as c
    join orders as o
      on o.cust_id = c.cust_id
where c.cust_id in 
      ( select cust_id 
        from orders
        group by cust_id
        having count(*) =  
                  ( select top (1) count(*) as cnt
                     from orders
                     group by cust_id
                     order by cnt desc
                  )
      ) ;

The subquery simplified using TOP WITH TIES:

select c.*, o.* 
from customers as c
    join orders as o
      on o.cust_id = c.cust_id
where c.cust_id in 
      ( select top (1) with ties m.cust_id 
        from orders as m
        group by m.cust_id
        order by count(*) desc
      ) ;

All of the above could be rewritten to use a derived table or CTE and JOIN as the subquery is uncorrelated.

Another way would be to use window functions in combination with TOP:

with 
  d as
  ( select c.*, o.*,
           cnt = count(*) over (partition by o.cust_id)  
    from customers as c
        join orders as o
          on o.cust_id = c.cust_id
  )
select top (1) with ties
    d.*
from d
order by d.cnt desc ; 

or just window functions:

with 
  d as
  ( select m.cust_id,
           cnt = count(*)  
           max_cnt = max(count(*)) over ()  
    from orders as m
    group by m.cust_id
  )
select c.*, o.* 
from customers as c
    join orders as o
      on o.cust_id = c.cust_id
    join d 
      on d.cust_id = c.cust_id
where d.cnt = d.max_cnt ;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.