How to get mysql to only return the latest order number & date combination?-CodePudding

I have a table in Metabase that has columns Called "Order Number" & "Order Close Date". If an order had a close date that got pushed to a later date, that order number will appear in both the original close date & in the new one. (So for example, if order 10 was supposed to close on 9/12/2022, but actually closed on 9/22/2022, the table has both | 9/12/2022| 10 | entry & | 9/22/2022| 10 | entry).

I need to get my query to only return the order number & corresponding latest closing date (so for the above example, it would be the 9/22/2022 one) for the month of September. I tried using this query:

Select 
distinct(order_number) as "Order Number", 
max(order_close_date) as "Close date"
From orders
Where order_close_date>='2022-09-01' and order_close_date<= '2022-09-30'

I'm not quite sure why, but the query returns all order numbers, including the repeat ones (so order 10 for both 9/12/2022 & 9/22/2022, which I suppose means it treats it as separate orders since they have different closing dates) & also returns column 'Close date' populated with the latest date in September - 9/30/2022 - for every single order instead of the latest date for that particular order.

I'm not too sure how to fix this query so it would return the output that I need & would appreciate any suggestions!

CodePudding user response：

Sounds like you want to GROUP BY the orders.

Try with

SELECT 
    order_number AS 'Order Number', 
    MAX(order_close_date) AS 'Close date'
FROM orders
WHERE order_close_date >= '2022-09-01' AND order_close_date <= '2022-09-30'
GROUP BY order_number