When you need select Min or Max value from table with one column, it's easy SQL task. A little harder is same request when you want to find minimum value and maximum value associated with column from minimal value row. For example, let's say we want see Customer with his minimum sales order plus amount value of this order and compare it to maximum value from any other order made with this customer.

Other common scenario can be find contra account to journal document. Find record with biggest minus amount and assign it as contra account to plus record.

For example if you try SQL on AdventureWorks2014 database

SELECT CustomerID, [SalesOrderNumber], Max(TotalDue) as MaxTotalDue, Min(TotalDue) as MinTotalDue FROM [AdventureWorks2014].[Sales].[SalesOrderHeader]
 WHERE CustomerID = 14324
 GROUP BY CustomerID, [SalesOrderNumber]

you will get 3 records

Instead of 1 record with lover order and max order amount for same customer, no matter which sales order.

Than we need use subquery

SELECT soh.CustomerID, soh.SalesOrderNumber, TotalDue, MinTotalDue, MaxTotalDue FROM 
 (SELECT CustomerID, Max(TotalDue) as MaxTotalDue, Min(TotalDue) as MinTotalDue FROM [AdventureWorks2014].[Sales].[SalesOrderHeader]
 GROUP BY CustomerID) mmv
 JOIN
 [AdventureWorks2014].[Sales].[SalesOrderHeader] soh
 ON 
 soh.CustomerID = mmv.CustomerID
 AND 
 soh.TotalDue = mmv.MinTotalDue
 WHERE soh.CustomerID = 14324

Here we choose CustomerID with Max and Min values and associate it with order having lowest amount.

This result we will get

With RowNumber and Partition By

Modified code using T-SQL features RowNumber and Partition By, can easy help identify lowest order to all orders for same customer (alternatively contra account etc.)

Select soh.CustomerID, soh.[SalesOrderNumber], soh.TotalDue, minAmount.[SalesOrderNumber], minAmount.TotalDue
 FROM (
      SELECT CustomerID, [SalesOrderNumber], TotalDue
      , ROW_NUMBER() OVER (PARTITION BY CustomerID ORDER BY TotalDue) AS [seq]
      FROM [AdventureWorks2014].[Sales].[SalesOrderHeader]
 ) minAmount
 JOIN [AdventureWorks2014].[Sales].[SalesOrderHeader] soh
 ON
 soh.CustomerID = minAmount.CustomerID
WHERE minAmount.seq = 1 AND soh.TotalDue > 0 AND soh.CustomerID = 14324
ORDER BY soh.CustomerID

Which returns 

This will becase extremly usefull, when you remove CustomerID in WHERE and run it for all Customers + Orders in table for some update.

Hope you will like this feature, that can be sometimes really helpful.

Tweet