subquery solution (handle more than one numbers occurring the same number of max times)

  • 1

    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)

  • 0

    @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.

  • 0

    @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 DISTINCT.

  • 0

    @lyn200 ... and if we replace the IN with =, it works w/o the DISTINCT

  • 0

    Oh I have the same question about the DISTINCT

Log in to reply

Looks like your connection to LeetCode Discuss was lost, please wait while we try to reconnect.