Find the largest number from the SQL Table?

Question

I was asked to find the largest number from the table (including all rows and columns) using SQL.

Here is the sample data. It should return 90 as the largest number.

row1    row2    row3
10      20      30
40      50      60
70      80      90

Answer 1

You can easily get that result selecting maxes from all columns, then pivoting that one row of maxes to column and again, taking max of that column. This code reflects that logic:

--sample data you provided
declare @table table(col1 int, col2 int, col3 int)
insert into @table values (10,20,30),(40,50,60),(70,80,90)
--query reflecting above logic
select max([maxes]) from (
    select max(col1) [col1],
           max(col2) [col2],
           max(col3) [col3]
    from @table
) [Maxes] unpivot (
    [maxes] for [values] in ([col1],[col2],[col3])
) as unpivoted

Answer 2

This isn't terribly reusable, but is very clear

DECLARE @t TABLE (row1 int,row2 int,row3 int)
INSERT INTO @t (row1,row2,row3) SELECT 10,20,30
INSERT INTO @t (row1,row2,row3) SELECT 40,50,60
INSERT INTO @t (row1,row2,row3) SELECT 70,80,90

DECLARE @Max INT

SELECT @Max=MAX(Row1) FROM @t
SELECT @Max=MAX(Row2) FROM @t WHERE (Row2>@Max OR @Max IS NULL)
SELECT @Max=MAX(Row3) FROM @t WHERE (Row3>@Max OR @Max IS NULL)

SELECT @Max AS [Max]