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