What is the best way to select a minimum value from multiple columns?

Question

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 a query that gives the following result (i.e. the one that gives the last column - a column containing the minium values ​​from 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 said in the comments) in a real scenario, the database is properly normalized . These “array” columns are not in the actual table, but are in the result set that is required in the report. And the new requirement is that the report also needs this MinValue column. I can’t change the basic result set, and so I searched for T-SQL for a convenient “exit from the prison card”.

I tried the CASE approach mentioned below and it works, although it is a bit cumbersome. It is also more complicated than indicated in the answers, because you need to take into account the fact that there are two min values ​​in one line.

In any case, I thought I would post my current solution, which, given my limitations, works very 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 will say in advance that I do not expect this to provide better performance, but given the circumstances (I cannot reverse engineer all the queries just for the new MinValue column requirement), this is a pretty elegant "get from jail card".

Answer 1

There are probably many ways to do this. My suggestion is to use Case / When to do this. With 3 columns, this is not so 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 

Answer 2

Using CROSS APPLY :

 SELECT ID, Col1, Col2, Col3, MinValue FROM YourTable CROSS APPLY (SELECT MIN(d) MinValue FROM (VALUES (Col1), (Col2), (Col3)) AS a(d)) A 

SQL Fiddle

Answer 3

 SELECT ID, Col1, Col2, Col3, (SELECT MIN(Col) FROM (VALUES (Col1), (Col2), (Col3)) AS X(Col)) AS TheMin FROM Table 

Answer 4

The best way to do this, probably not to do this, is that people insist on storing their data in a way that requires SQL "gymnastics" to extract meaningful information when there are much simpler ways to achieve the desired result if you just structure your schema a bit better :-)

The correct 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 additional key, depending on your needs). Then your query becomes simple select min(val) from tbl and you can still process the individual "old columns" separately, using, where col = 2 in your other queries. It also makes it easy to expand as the number of "old columns" increases.

This makes your queries a lot easier. The general rule that I usually use is that if you ever have something that looks like an array in a database row, you are probably doing something wrong and should consider restructuring the data.

---

However, if for some reason you cannot change these columns, I would suggest using insert and update triggers and add another column for which these triggers are set to at least Col1/2/3 . This will shift the "cost" of the operation from the choice to update / insert, to which it refers - most of the database tables in my experience are read much more often than they are written, so the cost of writing, as a rule, becomes more effective over time.

In other words, the minimum for a row changes only when one of the other columns changes, so that’s why you should calculate it, and not every time you choose (what is lost if the data does not change). Then you will get a table, for example:

 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 must make decisions during select usually a bad idea in terms of performance, since the data changes only when inserting / updating - adding another column takes up more space in the database and will be slightly slower for inserts and updates, but can be much faster for selection - the preferred approach should depend on your priorities, but, as already mentioned, most tables are read much more often than they are written.

Answer 5

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 condition is terminated, it ensures that Col1 is not the smallest value, so you can exclude it from the rest of the conditions. Similarly for the following conditions. For five columns, your query will look like this:

 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 relationship between two or more columns, then <= will ensure that we exit the CASE statement as early as possible.

Answer 6

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 stake, then the above will be

 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) 

Answer 7

You can also do this with a join request. As the number of columns increases, you will need to change the query, but at least it will be a direct change.

 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 

Answer 8

Use this:

 select least(col1, col2, col3) FROM yourtable 

Answer 9

This is brute force, but it 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 () only works with one column, not columns.

Answer 10

Both this question And this question try to answer this question.

The summary is that Oracle has a built-in function for this: with Sql Server you are stuck either by defining a user-defined function or by using case arguments.

Answer 11

If you can create a stored procedure, it can take an array of values, and you can just call it.

Answer 12

 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 

Answer 13

If you are using SQL 2005, you can do something 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 

Thus, you are not lost in many operators :)

However, this may be slower than the other option.

This is your choice...

Answer 14

A small twist in the join request:

 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 

Answer 15

Below I use a temporary table to get at least a few dates. The first temp table queries for several related tables to get different dates (as well as other values ​​for the query), the second temp table gets different columns and the minimum date, using as many passes as there are date columns.

This essentially looks like a join request, it requires the same number of passes, but can be more efficient (based on experience, but testing will be required). Efficiency was not a problem in this case (8000 entries). You can 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 

Answer 16

For multiple columns, it is best to use the CASE operator, however for two numeric columns i and j you can use simple math:

min (i, j) = (i + j) / 2 - abs (ij) / 2

This formula can be used to get the minimum value of several columns, but its really dirty past 2, min (i, j, k) will be min (i, min (j, k))

Answer 17

 SELECT [ID], ( SELECT MIN([value].[MinValue]) FROM ( VALUES ([Col1]), ([Col1]), ([Col2]), ([Col3]) ) AS [value] ([MinValue]) ) AS [MinValue] FROM Table; 

Answer 18

If you know what values ​​you are looking for, usually a status code, the following may be useful:

 select case when 0 in (PAGE1STATUS ,PAGE2STATUS ,PAGE3STATUS, PAGE4STATUS,PAGE5STATUS ,PAGE6STATUS) then 0 else 1 end FROM CUSTOMERS_FORMS 

Answer 19

I know this question is old, but I still needed an answer and was not happy with the other answers, so I had to come up with my own, which distorts @paxdiablo's answer .

---

I came from the country of SAP ASE 16.0, and I only needed to look at the statistics of certain data that IMHO are reliably stored in different columns of one row (they represent different times - when something was planned to arrive, what was expected, when the action started and finally, what was the actual time). Thus, I moved the columns to the rows of the temporary table and prepared my query as usual.

NB Not the only solution for everyone!

 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 

It took about 30 seconds in the initial set of 630,000 rows and used only index data, so not for starting in a time-critical process, but for things like a one-time data check or report at the end of the day, you may be fine (but check this with your peers or superiors, please!). The main advantage of this style for me was that I could easily use more / less columns and change the grouping, filtering, etc., especially after copying the data.

The extra data ( columnName , max es, ...) should have helped me in my search, so you might not need it; I left them here to possibly spark some ideas :-).