use max() in the derived table returned unexpected result-CodePudding

Write an SQL query to find the customer_number for the customer who has placed the largest number of orders.

Input: 
Orders table:
 -------------- ----------------- 
| order_number | customer_number |
 -------------- ----------------- 
| 1            | 1               |
| 2            | 2               |
| 3            | 3               |
| 4            | 3               |
 -------------- ----------------- 
Output: 
 ----------------- 
| customer_number |
 ----------------- 
| 3               |
 -----------------

my SQL:

select max(c.d),customer_number from (Select Count(order_number) as d,customer_number  from Orders group by customer_number) as c

expected result:

[2,3]

actual result:

[2,1]

CodePudding user response：

You could use a LIMIT query here:

SELECT customer_number, COUNT(*) AS num_orders
FROM Orders
GROUP BY customer_number
ORDER BY num_orders DESC
LIMIT 1;

Assuming you could have ties for two or more customers having the same maximum number of orders, then using RANK would be the easiest way on MySQL 8 :

WITH cte AS (
    SELECT customer_number, COUNT(*) AS num_orders,
           RANK() OVER (ORDER BY COUNT(*) DESC) AS rnk
    FROM Orders
    GROUP BY customer_number
)

SELECT customer_number, num_orders
FROM cte
WHERE rnk = 1;