Find the minimum or maximum value across several columns

Assume you have the following columns:
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

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

SELECT max(greatest(col1, col2) FROM TABLE
SELECT min(least(col1, col2) FROM TABLE

OK, you asked for ANY answer, so this should work with most dialects:

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

How about this?
SELECT max(c) FROM
  (SELECT col1 AS c FROM t1
   UNION
   SELECT col2 AS c FROM t1) AS t2

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

too expensive on select

One math solution:

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

case
when Col1 < Col2 then Col1
else Col2
end as NewCol