Как найти минимальное значение в таблице sql

We can also find the highest or lowest value from two or more values using the MAX() and MIN() function. For example,

-- returns 50 as highest
SELECT MAX(20, 30, 50) as highest;

-- returns 20 as lowest
SELECT MIN(20, 30, 50) as lowest;

Let’s see how we can use MAX() and MIN() function with HAVING:

SELECT *
FROM Customers
GROUP BY country
HAVING MAX(age);

Here, the SQL command returns the maximum age in each country from the Customers table.

To learn more, visit SQL HAVING Clause.

asked Dec 15, 2008 at 13:27

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

answered Dec 15, 2008 at 13:39

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

SQL Fiddle

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

The OP was tagged as sql-server, and this is an SQL Server specific answer. May not work on other DBMSs. (Search for least for Postgres derived DBs and some others).

answered Jan 4, 2016 at 11:03

On MySQL, use this:

select least(col1, col2, col3) FROM yourtable

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.

answered Mar 2, 2015 at 13:09

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)

answered Aug 28, 2012 at 13:48

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.

answered Dec 15, 2008 at 13:43

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

answered Dec 15, 2008 at 13:51

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.

answered Dec 15, 2008 at 13:40

answered Dec 16, 2008 at 2:40

crowleycrowley

1001 silver badge5 bronze badges

answered Dec 15, 2008 at 13:44

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

answered Dec 15, 2008 at 13:46

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…

Israel MarguliesIsrael Margulies

8,6062 gold badges30 silver badges26 bronze badges

RaoRao

7827 silver badges17 bronze badges

answered Jul 19, 2021 at 23:43