06.12.08

Max value among three columns

Posted in Important SQL Query, Interview Questions, SQL Server, Utlities tagged , , , at 8:59 am by dayananthan

 

The following query will fetch you the max value among three columns
Create Table #test (Name Char(10), Qty1 INT, QTY2 INT, QTY3 INT)

Insert into #Test (Name, QTY1, QTY2, QTY3) Values (‘Monitor’,12,13,11)

Insert into #Test (Name, QTY1, QTY2, QTY3) Values (‘KeyBoard’,8,7,6)

Insert into #Test (Name, QTY1, QTY2, QTY3) Values (‘Mouse’,3,2,5)

–Query to get the max among three columns

select [name],(select max(Qty)

                     from   (select QTY1 as Qty

                             union all

                             select QTY2 as Qty

                             union all

                             select QTY3) as Qty) as Maxi

from #test