How to select last order_no and order_date for particular Customer

Question

I want to select last order_no and order_date for group by (customer_key)

DATA TABLE NAME Cust_order
Order_id Order_no Order_date Customer_key 1 FL/BSR/130221/21 13.02.21 1233325 2 FL/BDR/080321/69 08.03.21 550089 3 FL/BSR/210322/30 21.03.22 1233325 4 FL/BSR/161221/28 16.12.21 1233325 5 FL/GZL/300722/33 30.07.22 1239689 6 FL/BDR/080220/52 08.02.20 550089 7 FL/GZL/231221/54 23.12.21 1239689 8 FL/GZL/190422/66 19.04.22 1239689

OUTPUT TABLE
Order_no Order_date Customer_key FL/BDR/080321/69 08.03.21 550089 FL/BSR/210322/30 21.03.22 1233325 FL/GZL/300722/33 30.07.22 1239689

My probable query as below

select Customer_key, max(Order_date) as DATE from Cust_order WHERE Customer_key in (1233325,550089,1239689) group by Customer_key

But I am unable to select last Order_no according to last Order_date for the customer

Answer 1

We can use ROW_NUMBER here:

WITH cte AS (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY Customer_key ORDER BY Order_date DESC) rn
    FROM yourTable
    WHERE Customer_key IN (1233325, 550089, 1239689) 
)

SELECT Order_no, Order_date, Customer_key
FROM cte
WHERE rn = 1;