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
soh.CustomerID = mmv.CustomerID
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
SELECT CustomerID, [SalesOrderNumber], TotalDue
, ROW_NUMBER() OVER (PARTITION BY CustomerID ORDER BY TotalDue) AS [seq]
JOIN [AdventureWorks2014].[Sales].[SalesOrderHeader] soh
soh.CustomerID = minAmount.CustomerID
WHERE minAmount.seq = 1 AND soh.TotalDue > 0 AND soh.CustomerID = 14324
ORDER BY soh.CustomerID
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.