Find the value of one field that matches the maximum data value in another field

Question

I am trying to write a query that gets the value of one field associated with the maximum value of another field (or fields). Let's say I have the following data table:

OrderID CustomerID  OrderDate   LocationID          
1       4           1/1/2001    1001
2       4           1/2/2001    1003
3       4           1/3/2001    1001
4       5           1/4/2001    1001
5       5           1/5/2001    1001
6       5           1/6/2001    1003
7       5           1/7/2001    1002
8       5           1/8/2001    1003
9       5           1/8/2001    1002

Grouping CustomerID, I want to get the maximum OrderDate, and then LocationID, related to what is the maximum OrderDate. If there are several records that share the maximum order date, then take the LocationIDone associated with the maximum OrderIDfrom the number of records with the maximum date.

The final data set should look like this:

CustomerID  OrderDate   LocationID      
4           1/3/2001    1001
5           1/8/2001    1002

, . SQL , .

Answer 1

with cte As
(
select *, 
        row_number() over (partition by CustomerID 
                           order by OrderDate desc, OrderId desc) as rn
from yourtable
)
select CustomerID, OrderDate,LocationID
from cte 
where rn=1;

Answer 2

SELECT
   C.Name,
   C.CustomerID,
   X.*
FROM
   Customers C
   CROSS APPLY (
      SELECT TOP 1 OrderDate, LocationID
      FROM Orders O
      WHERE C.CustomerID = O.CustomerID
      ORDER BY OrderDate Desc, OrderID Desc
   ) X

Customers, , , .

, Row_Number, , , . - , CROSS APPLY . , .

Answer 3

, , :

select customerId,orderDate,locationId
  from orders o1
 where orderDate = (
           select top 1 orderdate
             from orders o2
            where o1.customerId = o2.customerId
            order by orderdate desc
       )