How to SELECT Min/Max value with associated column from same row

SQL icon baseWhen 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

CustomerID SalesOrderNumber MaxTotalDue MinTotalDue
 14324  SO49619  2264.2536  2264.2536
 14324  SO65949  858.9607  858.9607
 14324  SO66488  2535.964  2535.964

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

CustomerID SalesOrderNumber TotalDue MaxTotalDue MinTotalDue
 14324 SO65949  858.9607  858.9607  2264.2536

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 

CustomerID SalesOrderNumber TotalDue SalesOrderNumber TotalDue
 14324  SO49619  2264.2536  SO65949  858.9607
 14324  SO65949  858.9607  SO65949  858.9607
 14324  SO66488  2535.964  SO65949  858.9607

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.

Author info
Author: Stanislav DubenWebsite: http://www.duben.org
About me
I am experienced database engineer with more than 12 years developing and optimization experience. I worked on many high level projects based on SQL servers. I am also photograper and owner of many internet projects.


Add comment

Security code
Refresh