Minimum or Maximum Values from Entire Table or from Multiple Tables in SQL

Minimum or Maximum Values from Entire Table or from Multiple Tables in SQL

This is the comman question by interviewer when you are going for interview yesterday one of my college time junior called me and asked that he went to interview and the interview has given him a table with 5 columns and told him to retirve the max and minimum value So i told him the answer and thought to write a blog on this.

So as per the above question you have a table having 5 column and you want to retrive max and min value from the table

Let me create a table with 5 columns

create table table1

(col1 int ,col2 int, col3 int ,col4 int,col5 int)

insert into table1 values(1,3,5,6,7),(10,11,24,44,12),(12,44,55,64,54),(11,22,44,55,100)

select * from table1 

Output

col1 col2 col3 col4 col5

1 3 5 6 7

10 11 24 44 12

12 44 55 64 54

11 22 44 55 100

Now if you will see that the minimum value in entire table is 1 and maximum is 100 

so how to retrieve this there are many ways but i will tell u the simpler and easier

Select MIN(d.col1)as Minimum,MAX(d.col1 )as Maximum from

(

select col1 from table1 

union --Union to take only distinct values we can use union all also

select col2  from table1 

union 

select col3  from table1 

union 

select col4  from table1 

union 

select col5  from table1 

)D

Output

Minimum Maximum

1           100

Same thing you can apply where you have more than 1 table and you are asked to calculate min or max or both from 2 or 3 tables

Simply add more union in the inner query .

Select MIN(d.col1)as Minimum,MAX(d.col1 )as Maximum from

(

select col1 from table1 

union --Union to take only distinct values we can use union all also

select col2  from table1 

union 

select col3  from table1 

union 

select col4  from table1 

union 

select col5  from table1 

union

select col1 from table2--Table 2 Column Names

union

select col2  from table2 

union 

select col3  from table2 

)D

Isn't its Simple if you want to try Another way you can use by using Derived table or CTE like taking minimum and maximum value 

of each column putting into derived table and CTE and From those min and max values picking the most minimum and most maximum Value.