0

I am looking for a SQL Server query which returns the maximum value of a table containing only int (or floats):

create table tab 
(
    temp1 int,
    temp2 int,
    temp3 int,
    temp4 int
)

insert into tab 
values (1, 2, 3, 4),
       (5, 6, 7, 8),
       (9, 10, 11, 12)

I want to get 12 returned.

Is it possible in a simple way?

GMB
  • 216,147
  • 25
  • 84
  • 135
francesco fabbro
  • 9
  • 1

4 Answers4

1

In SQL Server, the simplest approach probably is to unpivot with a lateral join to unpivot, then top():

select top (1) val
from tab
cross apply (values (temp1), (temp2), (temp3), (temp4)) x(val)
order by val desc

Or using aggregation rather than ordering:

select max(val) val
from tab
cross apply (values (temp1), (temp2), (temp3), (temp4)) x(val)
GMB
  • 216,147
  • 25
  • 84
  • 135
1
--fixed number of cols: temp1-4
select max(unp.theint)
from tab as t
unpivot 
(
theint FOR thecol IN ( temp1, temp2, temp3, temp4)
) as unp;


--any number of cols
alter table tab add col1 int default(10) with values;
alter table tab add colx int default(100) with values;

select max(theint)
from
(
select (select t.* for xml path(''), type).query('max(/*)').value('.', 'int') as theint
from tab as t
) as src;
lptr
  • 1
  • 2
  • 6
  • 16
1

You must use this code snippet

SELECT MAX(TEMP) AS MAXTEMP
FROM DBO.TAB
UNPIVOT ( TEMP FOR TEMPVal IN ( TEMP1, TEMP2, TEMP3 , TEMP4) ) AS u
mohade3 lotfi
  • 41
  • 2
0

In a simple way no. By using dynamic SQL yes. Such a design for a table is violating the normalisation rules, and that is the cause of the poor ability to write a simple and efficient query

SQLpro
  • 3,994
  • 1
  • 6
  • 14