Question 240629 in Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – Having trouble using MIN() in having clause

Trying to select earliest order_date for each customer_id.
Using following query

SELECT customer_id, order_date
    FROM Delivery
    GROUP BY customer_id
    HAVING MIN(order_date) = order_date

But those customer_id with more than one order_date rows are not showing in the result. For example id = 7 has order_date ‘ 2019-07-22’ and ‘2019-07-08’. Running the query above, id = 7 is not showing in the result. Was wondering what cuase such an issue and how to address it?

I know I can get around with subquery and where clause as such. I really want to know what went wrong in the having clause.

WHERE (customer_id, order_date) IN (SELECT customer_id, MIN(order_date)
   FROM Delivery
   GROUP BY customer_id)
Tags:

2

Answers


  1. 0

    For SQL SERVER, Try to use ROW_NUMBER

    declare @delivery table (customer_id int, order_date date)

insert into @delivery values (1, '1-jan-2023')
, (1, '2-jan-2023')
, (1, '3-jan-2023')
, (2, '2-jan-2023')
, (2, '3-jan-2023')

declare @targetOrderDate date = '2-Jan-2023'

select 
a.customer_id
,a.order_date
from (
    select 
    ROW_NUMBER() OVER(PARTITION BY customer_id ORDER BY order_date ASC) r_num
    ,a.customer_id
    ,a.order_date
    from @delivery a
) AS a
WHERE a.r_num = 1
    and a.order_date = @targetOrderDate

    To list customer first order, you can comment the and a.order_date = @targetOrderDate.

    To list the customer last order, you can change the ORDER BY order_date ASC to ORDER BY order_date DESC

    Login or Signup to reply.
  2. 0

    The MIN(order_date) function does return the smallest order_date for each customer_id group, but the order_date in your HAVING clause is just the order_date of an arbitrary row from each group, not necessarily the row with the smallest order_date.

    i.e. In your query the HAVING clause excludes any groups where the order_date of the arbitrary row chosen by the GROUP BY clause isn’t the smallest order_date in the group. (This is why customer_id 7 is not in your result, but note, in a different run of the query different results might occur.)

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search