Getting max and latest rows in SQL

Question

I have a table containing Orders, where in the same day multiple orders can be created for a given Name. I need to return the latest Order for a given date and name, and if there are multiple orders on that day for a name, return the one with the largest order value.

Sample data:

  ID  | NAME |  OrderDate   |   OrderValue
  ----+------+--------------+--------------
  1   | A    | 2019-01-15   |   100
  2   | B    | 2019-01-15   |   200
  3   | A    | 2019-01-15   |   150
  4   | C    | 2019-01-17   |   450
  5   | D    | 2019-01-18   |   300
  6   | C    | 2019-01-17   |   500

Result returned should be:

  ID  | NAME |  OrderDate   |   OrderValue
  ----+------+--------------+--------------
  2   | B    | 2019-01-15   |   200
  3   | A    | 2019-01-15   |   150
  5   | D    | 2019-01-18   |   300
  6   | C    | 2019-01-17   |   500

I can do this in multiple SQL queries, but is there a simplistic query to achieve the above result?

Answer 1

Starting SQL Server 2005, just use ROW_NUMBER():

SELECT ID, Name, OrderDate, OrderValue
FROM (
    SELECT 
        o.*, 
        ROW_NUMBER() OVER(PARTITION BY Name, OrderDate ORDER BY OrderValue DESC) rn
    FROM orders o
) x WHERE rn = 1

ROW_NUMBER() assigns a rank to each record within groups of records having the same Name and OrderDate, sorted by OrderValue. The record with the highest order value gets row number 1.

---

With older versions, a solution to filter the table is to use a correlated subquery with a NOT EXITS condition :

SELECT ID, Name, OrderDate, OrderValue
FROM orders o
WHERE NOT EXISTS (
    SELECT 1 
    FROM orders o1
    WHERE 
        o1.Name = o.Name 
        AND o1.OrderDate = o.OrderDate 
        AND o1.OrderValue > o.OrderValue
)

The NOT EXISTS condition ensures that there is no other record with a highest OrderValue for the same Name and OrderDate.