I have a question about the difference in the operators = and in.
Previously I used operator in in most of cases, but I found it is not working in today's quiz. The question is as following:
Query the customer_number from the orders table for the customer who has placed the largest number of orders.
It is guaranteed that exactly one customer will have placed more orders than any other customer.
The orders table is defined as follows:
| Column | Type |
|-------------------|-----------|
| order_number (PK) | int |
| customer_number | int |
| order_date | date |
| required_date | date |
| shipped_date | date |
| status | char(15) |
| comment | char(200) |
Sample Input
| order_number | customer_number | order_date | required_date | shipped_date | status | comment |
|--------------|-----------------|------------|---------------|--------------|--------|---------|
| 1 | 1 | 2017-04-09 | 2017-04-13 | 2017-04-12 | Closed | |
| 2 | 2 | 2017-04-15 | 2017-04-20 | 2017-04-18 | Closed | |
| 3 | 3 | 2017-04-16 | 2017-04-25 | 2017-04-20 | Closed | |
| 4 | 3 | 2017-04-18 | 2017-04-28 | 2017-04-25 | Closed | |
Sample Output
| customer_number |
|-----------------|
| 3 |
And my approach is:
select
customer_number
from orders
group by customer_number
having count(*) in
(
select
max(total)
from (
select
count(*) as total
from orders
group by customer_number
) as d
)
But it won't produce any result. If I replace the in with =, I can get what I would like. Could anyone explain this?