Get minimum value from multiple columns in SQL Server

This post will discuss how we can extract the minimum value from multiple columns in SQL Server. For example, we have a table that stores the temperature of multiple cities in columns. The temperature data of each city is stored in a separate column. However, we have to select the minimum temperature value throughout all the cities.

In order to create the sample data we can use below query. This will help us to create the dummy table with some data points.

CREATE TABLE dbo.TestTable
(
	Id INT IDENTITY(1,1),
	dDate DATE,
	MumbaiTemp NUMERIC(18,2),
	NewDelhiTemp NUMERIC(18,2),
	ChennaiTemp NUMERIC(18,2),
	KolkataTemp NUMERIC(18,2)
)
GO

INSERT INTO dbo.TestTable
(dDate, MumbaiTemp, NewDelhiTemp, ChennaiTemp, KolkataTemp)
VALUES
('20100101', 38, 35, 33, 34),
('20110101', 35, 32, 31, 37),
('20120101', 37, 31, 36, 40),
('20130101', 36, 30, 35, 39),
('20140101', 32, 28, 32, 41)
GO

SELECT * FROM dbo.TestTable
GO

In the above image, we can see that the minimum temperature value is 28 for the NewDelhiTemp column. So, we need to write a query to get this minimum value throughout multiple columns.

Method 1 – Get minimum value from multiple columns using a CASE statement

We can use a nested CASE statement to compare the values of multiple columns to get the minimum value. Also, we can use the nested IIF statement. The query should look like this.

SELECT 
MIN(MinTemp) AS MinTemp
FROM 
(
SELECT 
CASE WHEN MumbaiTemp < NewDelhiTemp THEN MumbaiTemp
	 WHEN NewDelhiTemp < ChennaiTemp THEN NewDelhiTemp
	 WHEN ChennaiTemp < KolkataTemp THEN ChennaiTemp
	 ELSE KolkataTemp END 
AS MinTemp FROM
dbo.TestTable
)dt

Output

Method 2 – Get minimum value from multiple columns using a UNION ALL query

An UNION ALL query can also help us to move these multiple columns into a single row. Then, we can use the MIN function to get the minimum value throughout all the columns. The query should look like this.

SELECT MIN(Temp) AS MinTemp
FROM
(
SELECT MumbaiTemp AS Temp FROM dbo.TestTable
UNION ALL
SELECT NewDelhiTemp AS Temp FROM dbo.TestTable
UNION ALL
SELECT ChennaiTemp AS Temp FROM dbo.TestTable
UNION ALL
SELECT KolkataTemp AS Temp FROM dbo.TestTable
)dt

Output

Method 3 – Get minimum value from multiple columns using an UNPIVOT clause

Using UNPIVOT clause, we can move all these columns into rows and then we can apply the MIN function to get the minimum value. The query should look like this.

SELECT MIN(Temp) AS MinTemp 
FROM dbo.TestTable
UNPIVOT (Temp FOR DateVal IN (MumbaiTemp, NewDelhiTemp, ChennaiTemp, KolkataTemp)) AS u

Output

Thanks for the reading. Please share your inputs in the comment section.