function for highest value across columns

Question

Post reply

function for highest value across columns

EamonSQL

SSCrazy

Points: 2307
More actions
June 24, 2016 at 4:42 am

#97477

Hi,
I have a table with 4 integer columns and need to know whether there is a function that will return the highest value across these columns.
e.g. SELECT highest_val(col1,col2,col3,col4 from dbo.mytable
I've been playing with case statements, pivots and creating derived tables with unions etc to achieve this but I just thought I'd throw it out in case I've missed any function I'm now aware of.
I'm using S2K2008.
Thanks,
Eamon

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Umer Akram SSC-Insane Points: 22209 More actions · Answer 1

Something like this:

Declare @Table table

(

id int identity(1,1),

col1 int,

col2 int,

col3 int,

col4 int

)

insert into @Table

Select 1,2,3,4 union all -- 4 highest

Select 5,6,7,8 -- 8 highest

Select *

from

(

Select * , ROW_NUMBER() OVER (partition by id order by x.col desc) as getmaxcol

from @Table

Cross apply

(

Values ('Col1',Col1) , ('col2', col2) , ('col3', col3), ('col4', col4)

)x(colname, col)

) A

where a.getmaxcol = 1

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 2

Simpler:

SELECT *

FROM (SELECT col1 = 1, col2 = 2, col3 = 3, col4 = 4) s

CROSS APPLY (

SELECT MAXmyval = MAX(d.myval)

FROM (VALUES (col1), (col2), (col3), (col4)) d (myval)

) x

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

EamonSQL SSCrazy Points: 2307 More actions · Answer 3

Thanks alot, that works.

I think though upgrading our server is probably the real answer!

Umer Akram SSC-Insane Points: 22209 More actions · Answer 4

you can change your logic to this if u r on SQL2000.

Declare @Val1 int= 1

, @Val2 int= 2

, @Val3 int= 3

, @Val4 int= 4

;

Select MAx(val)

from

(

Select @Val1 as val union

Select @Val2 as val union

Select @Val3 as val union

Select @Val4

) A

EamonSQL SSCrazy Points: 2307 More actions · Answer 5

I think for this scenario a case statement works best for me.

thanks alot for the replies, I appreciate it.

Thanks,

Eamon

Sergiy SSC Guru Points: 110206 More actions · Answer 6

EamonSQL (6/24/2016)
Thanks alot, that works.
I think though upgrading our server is probably the real answer!

Following data normalisation rules - that's your real answer.

_____________
Code for TallyGenerator