0

I'm trying to select the most recent order for each customer in SQL Server. I need to return the customer and order number so it can be joined to other order information in a wider query.

Example Data

Table: CUST_ORDERS

Customer Order_No Order_Date_Time
Big Corp 4 2022-01-31 11:31:44.190
Big Corp 5 2022-01-31 11:31:46.817
Small Ltd 2 2022-01-31 10:28:15.560
Small Ltd 3 2022-01-31 10:28:18.207
Med plc 1 2022-01-31 11:39:11.453
Med Plc 2 2022-01-31 11:39:14.080

I'm creating a unique key by concatenating Customer and Order_Date_Time to try to filter the table to just those rows for the most recent order like this:

SELECT Customer,
       Order_No,
       Order_Date_Time
FROM CUST_ORDERS
WHERE CONCAT(Customer, Order_Date_Time)IN (SELECT CONCAT(Customer, MAX(Order_Date_Time))
                                           FROM CUST_ORDERS
                                           GROUP BY Customer);

The nested query (select CONCAT(Customer, MAX(Order_Date_Time)) from CUST_ORDERS group by Customer) is obviously working by itself, but when included in the where clause it's returning all of the rows in my example table rather than just those for the most recent order.

My questions are:

  1. So I understand more, how can this query return rows that aren't included in the nested query?
  2. How can I get round it?

Desired output:

Customer Order_No Order_Date_Time
Big Corp 5 2022-01-31 11:31:46.817
Small Ltd 3 2022-01-31 10:28:18.207
Med Plc 2 2022-01-31 11:39:14.080

** FYI, this is a dummy example. In the real data set I can't just use the highest order number.

Larnu
  • 76,706
  • 10
  • 34
  • 63
Steve
  • 31
  • 5
  • Just stop - doom awaits you with this seemingly clever trick of concatenating columns to use with IN. Use EXISTS with the appropriate correlation logic for that generally. But you don't need that at all; rather it seems you have a standard and very common "first in group" need. Search that phrase to find many examples. – SMor May 03 '22 at 11:01

0 Answers0