0

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

Salman
  • 3
  • 2
  • 2
    But you can have multiple data types, few columns will be of Integer, few will be date/varchar. How you can know the max between a date and an integer? Example: What is the maximum value between 1,'A' and '2022-05-19'? – Abinash May 19 '22 at 14:42
  • all columns are in integer – Salman May 19 '22 at 14:43
  • 1
    I removed the conflicting DBMS tags. Please add only **one** tag for the database product you are really using (and "SSMS" is not a database product, it's a SQL client tool for Microsoft SQL Server) –  May 19 '22 at 14:49
  • This is just a duplicate, see here: https://stackoverflow.com/questions/71022/sql-max-of-multiple-columns. To resume this link: The most simple way is using greatest, but you could also write a case when or sth else. – Jonas Metzler May 19 '22 at 15:39

3 Answers3

0

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.

Christian Amado
  • 946
  • 1
  • 7
  • 31
0

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

Abinash
  • 554
  • 2
  • 6
0

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;