What's the best way to select the minimum value from several columns?
SqlSql ServerTsqlSql Server-2005MinSql Problem Overview
Given the following table in SQL Server 2005:
ID Col1 Col2 Col3
-- ---- ---- ----
1 3 34 76
2 32 976 24
3 7 235 3
4 245 1 792
What is the best way to write the query that yields the following result (i.e. one that yields the final column - a column containing the minium values out of Col1, Col2, and Col 3 for each row)?
ID Col1 Col2 Col3 TheMin
-- ---- ---- ---- ------
1 3 34 76 3
2 32 976 24 24
3 7 235 3 3
4 245 1 792 1
UPDATE:
For clarification (as I have said in the coments) in the real scenario the database is properly normalized. These "array" columns are not in an actual table but are in a result set that is required in a report. And the new requirement is that the report also needs this MinValue column. I can't change the underlying result set and therefore I was looking to T-SQL for a handy "get out of jail card".
I tried the CASE approach mentioned below and it works, although it is a bit cumbersome. It is also more complicated than stated in the answers because you need to cater for the fact that there are two min values in the same row.
Anyway, I thought I'd post my current solution which, given my constraints, works pretty well. It uses the UNPIVOT operator:
with cte (ID, Col1, Col2, Col3)
as
(
select ID, Col1, Col2, Col3
from TestTable
)
select cte.ID, Col1, Col2, Col3, TheMin from cte
join
(
select
ID, min(Amount) as TheMin
from
cte
UNPIVOT (Amount for AmountCol in (Col1, Col2, Col3)) as unpvt
group by ID
) as minValues
on cte.ID = minValues.ID
I'll say upfront that I don't expect this to offer the best performance, but given the circumstances (I can't redesign all the queries just for the new MinValue column requirement), it is a pretty elegant "get out of jail card".
Sql Solutions
Solution 1 - Sql
There are likely to be many ways to accomplish this. My suggestion is to use Case/When to do it. With 3 columns, it's not too bad.
Select Id,
Case When Col1 < Col2 And Col1 < Col3 Then Col1
When Col2 < Col1 And Col2 < Col3 Then Col2
Else Col3
End As TheMin
From YourTableNameHere
Solution 2 - Sql
Using CROSS APPLY:
SELECT ID, Col1, Col2, Col3, MinValue
FROM YourTable
CROSS APPLY (SELECT MIN(d) AS MinValue FROM (VALUES (Col1), (Col2), (Col3)) AS a(d)) A
Solution 3 - Sql
SELECT ID, Col1, Col2, Col3,
(SELECT MIN(Col) FROM (VALUES (Col1), (Col2), (Col3)) AS X(Col)) AS TheMin
FROM Table
Solution 4 - Sql
On MySQL, use this:
select least(col1, col2, col3) FROM yourtable
Solution 5 - Sql
You can use the "brute force" approach with a twist:
SELECT CASE
WHEN Col1 <= Col2 AND Col1 <= Col3 THEN Col1
WHEN Col2 <= Col3 THEN Col2
ELSE Col3
END AS [Min Value] FROM [Your Table]
When the first when condition fails it guarantees that Col1 is not the smallest value therefore you can eliminate it from rest of the conditions. Likewise for subsequent conditions. For five columns your query becomes:
SELECT CASE
WHEN Col1 <= Col2 AND Col1 <= Col3 AND Col1 <= Col4 AND Col1 <= Col5 THEN Col1
WHEN Col2 <= Col3 AND Col2 <= Col4 AND Col2 <= Col5 THEN Col2
WHEN Col3 <= Col4 AND Col3 <= Col5 THEN Col3
WHEN Col4 <= Col5 THEN Col4
ELSE Col5
END AS [Min Value] FROM [Your Table]
Note that if there is a tie between two or more columns then <= ensures that we exit the CASE statement as early as possible.
Solution 6 - Sql
The best way to do that is probably not to do it - it's strange that people insist on storing their data in a way that requires SQL "gymnastics" to extract meaningful information, when there are far easier ways to achieve the desired result if you just structure your schema a little better :-)
The right way to do this, in my opinion, is to have the following table:
ID Col Val
-- --- ---
1 1 3
1 2 34
1 3 76
2 1 32
2 2 976
2 3 24
3 1 7
3 2 235
3 3 3
4 1 245
4 2 1
4 3 792
with ID/Col as the primary key (and possibly Col as an extra key, depending on your needs). Then your query becomes a simple select min(val) from tbl and you can still treat the individual 'old columns' separately by using where col = 2 in your other queries. This also allows for easy expansion should the number of 'old columns' grow.
This makes your queries so much easier. The general guideline I tend to use is, if you ever have something that looks like an array in a database row, you're probably doing something wrong and should think about restructuring the data.
However, if for some reason you can't change those columns, I'd suggest using insert and update triggers and add another column which these triggers set to the minimum on Col1/2/3. This will move the 'cost' of the operation away from the select to the update/insert where it belongs - most database tables in my experience are read far more often than written so incurring the cost on write tends to be more efficient over time.
In other words, the minimum for a row only changes when one of the other columns change, so that's when you should be calculating it, not every time you select (which is wasted if the data isn't changing). You would then end up with a table like:
ID Col1 Col2 Col3 MinVal
-- ---- ---- ---- ------
1 3 34 76 3
2 32 976 24 24
3 7 235 3 3
4 245 1 792 1
Any other option that has to make decisions at select time is usually a bad idea performance-wise, since the data only changes on insert/update - the addition of another column takes up more space in the DB and will be slightly slower for the inserts and updates but can be much faster for selects - the preferred approach should depend on your priorities there but, as stated, most tables are read far more often than they're written.
Solution 7 - Sql
If the columns were integers as in your example I would create a function:
create function f_min_int(@a as int, @b as int)
returns int
as
begin
return case when @a < @b then @a else coalesce(@b,@a) end
end
then when I need to use it I would do :
select col1, col2, col3, dbo.f_min_int(dbo.f_min_int(col1,col2),col3)
if you have 5 colums then the above becomes
select col1, col2, col3, col4, col5,
dbo.f_min_int(dbo.f_min_int(dbo.f_min_int(dbo.f_min_int(col1,col2),col3),col4),col5)
Solution 8 - Sql
You could also do this with a union query. As the number of columns increase, you would need to modify the query, but at least it would be a straight forward modification.
Select T.Id, T.Col1, T.Col2, T.Col3, A.TheMin
From YourTable T
Inner Join (
Select A.Id, Min(A.Col1) As TheMin
From (
Select Id, Col1
From YourTable
Union All
Select Id, Col2
From YourTable
Union All
Select Id, Col3
From YourTable
) As A
Group By A.Id
) As A
On T.Id = A.Id
Solution 9 - Sql
This is brute force but works
select case when col1 <= col2 and col1 <= col3 then col1
case when col2 <= col1 and col2 <= col3 then col2
case when col3 <= col1 and col3 <= col2 then col3
as 'TheMin'
end
from Table T
... because min() works only on one column and not across columns.
Solution 10 - Sql
Both this question And this question try to answer this.
The recap is that Oracle has a built in function for this, with Sql Server you are stuck either defining a user-defined-function or using case statements.
Solution 11 - Sql
For multiple columns its best to use a CASE statement, however for two numeric columns i and j you can use simple math:
min(i,j) = (i+j)/2 - abs(i-j)/2
This formula can be used to get the minimum value of multiple columns but its really messy past 2, min(i,j,k) would be min(i,min(j,k))
Solution 12 - Sql
If you're able to make a stored procedure, it could take an array of values, and you could just call that.
Solution 13 - Sql
select *,
case when column1 < columnl2 And column1 < column3 then column1
when columnl2 < column1 And columnl2 < column3 then columnl2
else column3
end As minValue
from tbl_example
Solution 14 - Sql
If you use SQL 2005 you can do something neat like this:
;WITH res
AS ( SELECT t.YourID ,
CAST(( SELECT Col1 AS c01 ,
Col2 AS c02 ,
Col3 AS c03 ,
Col4 AS c04 ,
Col5 AS c05
FROM YourTable AS cols
WHERE YourID = t.YourID
FOR
XML AUTO ,
ELEMENTS
) AS XML) AS colslist
FROM YourTable AS t
)
SELECT YourID ,
colslist.query('for $c in //cols return min(data($c/*))').value('.',
'real') AS YourMin ,
colslist.query('for $c in //cols return avg(data($c/*))').value('.',
'real') AS YourAvg ,
colslist.query('for $c in //cols return max(data($c/*))').value('.',
'real') AS YourMax
FROM res
This way you don't get lost in so many operators :)
However, this could be slower than the other choice.
It's your choice...
Solution 15 - Sql
A little twist on the union query:
DECLARE @Foo TABLE (ID INT, Col1 INT, Col2 INT, Col3 INT)
INSERT @Foo (ID, Col1, Col2, Col3)
VALUES
(1, 3, 34, 76),
(2, 32, 976, 24),
(3, 7, 235, 3),
(4, 245, 1, 792)
SELECT
ID,
Col1,
Col2,
Col3,
(
SELECT MIN(T.Col)
FROM
(
SELECT Foo.Col1 AS Col UNION ALL
SELECT Foo.Col2 AS Col UNION ALL
SELECT Foo.Col3 AS Col
) AS T
) AS TheMin
FROM
@Foo AS Foo
Solution 16 - Sql
If you know what values you are looking for, usually a status code, the following can be helpful:
select case when 0 in (PAGE1STATUS ,PAGE2STATUS ,PAGE3STATUS,
PAGE4STATUS,PAGE5STATUS ,PAGE6STATUS) then 0 else 1 end
FROM CUSTOMERS_FORMS
Solution 17 - Sql
Below I use a temp table to get the minimum of several dates. The first temp table queries several joined tables to get various dates (as well as other values for the query), the second temp table then gets the various columns and the minimum date using as many passes as there are date columns.
This is essentially like the union query, the same number of passes are required, but may be more efficient (based on experience, but would need testing). Efficiency wasn't an issue in this case (8,000 records). One could index etc.
--==================== this gets minimums and global min
if object_id('tempdb..#temp1') is not null
drop table #temp1
if object_id('tempdb..#temp2') is not null
drop table #temp2
select r.recordid , r.ReferenceNumber, i.InventionTitle, RecordDate, i.ReceivedDate
, min(fi.uploaddate) [Min File Upload], min(fi.CorrespondenceDate) [Min File Correspondence]
into #temp1
from record r
join Invention i on i.inventionid = r.recordid
left join LnkRecordFile lrf on lrf.recordid = r.recordid
left join fileinformation fi on fi.fileid = lrf.fileid
where r.recorddate > '2015-05-26'
group by r.recordid, recorddate, i.ReceivedDate,
r.ReferenceNumber, i.InventionTitle
select recordid, recorddate [min date]
into #temp2
from #temp1
update #temp2
set [min date] = ReceivedDate
from #temp1 t1 join #temp2 t2 on t1.recordid = t2.recordid
where t1.ReceivedDate < [min date] and t1.ReceivedDate > '2001-01-01'
update #temp2
set [min date] = t1.[Min File Upload]
from #temp1 t1 join #temp2 t2 on t1.recordid = t2.recordid
where t1.[Min File Upload] < [min date] and t1.[Min File Upload] > '2001-01-01'
update #temp2
set [min date] = t1.[Min File Correspondence]
from #temp1 t1 join #temp2 t2 on t1.recordid = t2.recordid
where t1.[Min File Correspondence] < [min date] and t1.[Min File Correspondence] > '2001-01-01'
select t1.*, t2.[min date] [LOWEST DATE]
from #temp1 t1 join #temp2 t2 on t1.recordid = t2.recordid
order by t1.recordid
Solution 18 - Sql
SELECT [ID],
(
SELECT MIN([value].[MinValue])
FROM
(
VALUES
([Col1]),
([Col1]),
([Col2]),
([Col3])
) AS [value] ([MinValue])
) AS [MinValue]
FROM Table;
Solution 19 - Sql
case
when Col1 < Col2 and Col1 < Col3
then Col1
when Col2 is null and Col3 is null
then Col1
when Col1 < Col2 and Col3 is null
then Col1
when Col1 < Col3 and Col2 is null
then Col1
when Col2 < Col1 and Col2 < Col3
then Col2
when Col1 is null and Col3 is null
then Col2
when Col2 < Col1 and Col3 is null
then Col2
when Col2 < Col3 and Col1 is null
then Col2
when Col3 < Col1 and Col3 < Col2
then Col3
when Col1 is null and Col2 is null
then Col3
when Col3 < Col1 and Col2 is null
then Col3
when Col3 < Col2 and Col1 is null
then Col3
when Col2 = Col3
then Col2
when Col1 = Col3
then Col1
when Col1 = Col2
then Col1
when Col2 = Col3 and Col1 = Col3
then Col1
else null end as 'MIN'
Solution 20 - Sql
I know that question is old, but I was still in the need of the answer and was not happy with other answers so I had to devise my own which is a twist on @paxdiablo´s answer.
I came from land of SAP ASE 16.0, and I only needed a peek at statistics of certain data which are IMHO validly stored in different columns of a single row (they represent different times - when arrival of something was planned, what it was expected when the action started and finally what was the actual time). Thus I had transposed columns into the rows of temporary table and preformed my query over this as usually.
N.B. Not the one-size-fits-all solution ahead!
CREATE TABLE #tempTable (ID int, columnName varchar(20), dataValue int)
INSERT INTO #tempTable
SELECT ID, 'Col1', Col1
FROM sourceTable
WHERE Col1 IS NOT NULL
INSERT INTO #tempTable
SELECT ID, 'Col2', Col2
FROM sourceTable
WHERE Col2 IS NOT NULL
INSERT INTO #tempTable
SELECT ID, 'Col3', Col3
FROM sourceTable
WHERE Col3 IS NOT NULL
SELECT ID
, min(dataValue) AS 'Min'
, max(dataValue) AS 'Max'
, max(dataValue) - min(dataValue) AS 'Diff'
FROM #tempTable
GROUP BY ID
This took some 30 seconds on source set of 630000 rows and used only index-data, so not the thing to run in time-critical process but for things like one-time data inspection or end-of-the-day report you might be fine (but verify this with your peers or superiors, please!). Main bonus of this style for me was that I could readily use more/less columns and change grouping, filtering, etc., especially once data was copyied over.
The additional data (columnName, maxes, ...) were to aid me in my search, so you might not need them; I left them here to maybe spark some ideas :-).