Recently I came across a requirement to get the maximum value from multiple numeric columns of a table. In SQL Server there are several ways to get the MIN or MAX of multiple columns including methods using UNPIVOT, UNION, CASE, etc… However, the simplest method is by using FROM … VALUES i.e. table value constructor. Let’s see an example.
In this example, there is a table for items with five columns for prices. We have to get the highest price for the items. Here is the script for creating the table and adding some data in it.
/* Create a temp table */
CREATE TABLE #ItemBids
(
ItemID int NOT NULL IDENTITY(1,1),
ItemName varchar(20) NOT NULL,
BidPrice1 smallmoney,
BidPrice2 smallmoney,
BidPrice3 smallmoney,
BidPrice4 smallmoney,
BidPrice5 smallmoney,
);
/* Insert sample data */
INSERT INTO #ItemBids
(ItemName, BidPrice1, BidPrice2, BidPrice3, BidPrice4, BidPrice5)
VALUES ('Mountain Bike', 800.25, 820.30, 915.25, 750.00, 810.75),
('Comfort Bike', 1000.60, 925.00, 1050.50, 920.00, 995.10),
('Hybrid Bike', 970.25, 920.00, 1017.25, 1055.80, 905.15),
('Road Bike', 780.00, 890.80, 720.00, 915.00, 755.45),
('Commuting Bike', 600.25, 640.50, 725.75, 695.00, 683.00);
SELECT * FROM #ItemBids;
Here is the select statement to get the maximum bid price from all the five BidPrice columns for each item.
/* Get the max bid price for each item */
SELECT
ItemID, ItemName,
(SELECT MAX(HighestBidPrice)
FROM (VALUES
(BidPrice1),
(BidPrice2),
(BidPrice3),
(BidPrice4),
(BidPrice5))
AS value(HighestBidPrice))
AS HighestBidPrice
FROM #ItemBids;
Using this technique, you can find the MIN or MAX of multiple columns.
Read more SQL Tips.
Reference
- Read more about Table Value Constructor at Microsoft Docs.
amazing skill, i have question, basically, is it a correlated subquery? if so, basically, we should use an expression in WHERE. in this example, there is not any expression using as an condition, how can VALUES know for each row, we only get the current row istead of the whole table?(i think when we don’t use WHERE, then should be whole dataset.)