How to find maximum value in entire table using SQL-CodePudding

I have a table with multiple columns and wanted to find the maximum value in the entire table (across all columns), let me know if it is possible? if yes how

All columns are in integer data type

CodePudding user response：

You can use MAX and GREATEST to achieve this.

Data Set:

CREATE TABLE test
  (
     col1     INTEGER,
     col2  INTEGER,
     col3 INTEGER
  );

INSERT INTO test VALUES 
     (1,100,2 ),(2,300,3 ),(3,350, 400 );

You can achieve it using below.

SELECT Greatest(Max(col1), Max(col2), Max(col3)) as Max_Value
FROM   test;

DB Fiddle: Try it here

CodePudding user response：

In Postgres and Oracle (and I believe in MySQL as well) you can use:

select max(greatest(col_1, col_2, col_3, col_4))
from the_table;

CodePudding user response：

Supose that you have these columns:

ID (PK)
Column 1 (int)
Column 2 (int)
Column 3 (int)

You can use a SELECT with a UNION clause inside it, something like this:

SELECT ID, MAX(FindNumber) AS FoundedNumber
FROM
(
    SELECT ID, Column1 AS FindNumber
    FROM YourTable
    UNION
    SELECT ID, Column2 AS FindNumber
    FROM YourTable
    UNION
    SELECT ID, Column3 AS FindNumber
    FROM YourTable
) subselect
GROUP BY ID

This solution is for Microsoft SQL Server.