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?
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?