I think this can handle more than one numbers occurring the same number of max times:
SELECT customer_number FROM orders GROUP BY customer_number having count(distinct order_number) IN (SELECT MAX(countn) FROM (select count(distinct order_number) as countn FROM orders GROUP BY customer_number) sub)
@pxj5333 Thanks for sharing your answer. I came up with the same solution as yours for the follow-up question. However, if I remove the "distinct" keyword from the two count() functions, it will not return a right answer. Do you have any ideas why that happens? Thanks.
I tried the sub-query individually, SELECT MAX(countn) FROM (select count(order_number) as countn FROM orders GROUP BY customer_number). There is no difference whether "distinct" is there or not, but the combined query will not return the right answer if "distinct" is removed.
@lyn200 Actually only the
DISTINCT in the
HAVING clause makes the difference. I am also confused with this;
order_number is PK so it's unique, there should not be any difference w/ or w/o the
@lyn200 ... and if we replace the
=, it works w/o the
Looks like your connection to LeetCode Discuss was lost, please wait while we try to reconnect.