1. We have moved to a new forum! There may be a few things not working properly so please let us know if you find a bug. Remember to use the bbCode [ sql ] tag for SQL statements.
New XenForo forum! Google Ads

Find the minimum or maximum value across several columns?

Discussion in 'SQL - Questions and Answers' started by ben, Apr 20, 2006.

  1. ben Administrator

    Find the minimum or maximum value across several columns

    Assume you have the following columns:
    Code:
    col1  col2
2       4
1       2
3       5
    We want the answer to be 1 for minimum and 5 for maximum.

    This question was asked on the postgres.sql mailing list Gmane -- Mail To News And Back Again
    ben, Apr 20, 2006
    #1

  2. ben Administrator

    Dialect: Postgresql
    Postgresql has two commands that come in handy, one is greatest() the other least()

    SQL:

    
SELECT max(greatest(col1, col2) FROM table
    
SELECT min(least(col1, col2) FROM table
    ben, Apr 20, 2006
    #2

  3. Rob137 Guest

    Dialect: T-SQL
    OK, you asked for ANY answer, so this should work with most dialects:

    SQL:

    
if exists (select TABLE_NAME from INFORMATION_SCHEMA.TABLES where TABLE_NAME = 'MyMinMaxTable')
    
drop table MyMinMaxTable
    
exec sp_dboption 'TelairDB', 'select into/bulkcopy', 'true'
    

    
CREATE TABLE [dbo].[MyMinMaxTable] (
    
	[my_min] [decimal](16, 4) NULL ,
    
	[my_max] [decimal](16, 4) NULL
    
) ON [PRIMARY]
    
GO
    

    
insert into MyMinMaxTable
    
select min(ord_qty), max(ord_qty)
    
from AA_TestTable
    

    
insert into MyMinMaxTable
    
select min(sitm_qty), max(sitm_qty)
    
from AA_TestTable
    

    
insert into MyMinMaxTable
    
select min(this_qty), max(this_qty)
    
from AA_TestTable
    

    
insert into MyMinMaxTable
    
select min(that_qty), max(that_qty)
    
from AA_TestTable
    

    
insert into MyMinMaxTable
    
select min(other_qty), max(other_qty)
    
from AA_TestTable
    

    
select min(my_min) as abs_min, max(my_max) as abs_max
    
from MyMinMaxTable
    Rob137, May 8, 2006
    #3

  4. Dimitar Guest

    How about this?
    SQL:

    
SELECT max(c) FROM
    
  (SELECT col1 as c FROM t1
    
   UNION
    
   SELECT col2 as c FROM t1) as t2
    Dimitar, May 11, 2006
    #4

  5. Vj Guest

    Dialect: MS SQL
    SQL:

    
select min(s.t) as  minimum,max(s.t)  as maximum from (select col1 as t from t1 union select col2 as t from t1) s
    Vj, Mar 29, 2007
    #5

  6. Unregistered Guest

    Do yourself a favor - just use an insert trigger and do the evaluation in the trigger..

    too expensive on select
    Unregistered, Dec 4, 2007
    #6

  7. Robert from Czech Guest

    One math solution:

    For Min try:
    SQL:

    
SELECT 
    
Min((Col1 + Col2 - Col1 * SIGN( Col1 - Col2 ) - Col2 * SIGN( Col2 - Col1 ) ) DIV 2) AS MinVal
    
FROM table_name;
    For Max try:
    SQL:

    
SELECT 
    
Max((Col1 + Col2 - Col1 * SIGN( Col2 - Col1 ) - Col2 * SIGN( Col1 - Col2 ) ) DIV 2) AS MaxVal
    
FROM table_name;
    Robert from Czech, Jun 18, 2008
    #7

  8. Unregistered Guest

    case
    when Col1 < Col2 then Col1
    else Col2
    end as NewCol
    Unregistered, Aug 13, 2008
    #8

Share This Page