Oct 7, 2006

A scenario to ponder #2

Unlike the previous scenario, I am not tying this up with any SQL Server version. Here is the question.
I have a table maxnum with the following data:

create table maxnum (a int, b int, c int)

insert into maxnum values (1,2,3)
insert into maxnum values (4,3,5)
insert into maxnum values (23,44,23)
insert into maxnum values (23,45,45)


Now I need to get the result with 4 columns. a,b,c and the max value of a,b,c. So, the result will be this way:

a b c maxval
1 2 3 3
4 3 5 5
23 44 23 44
23 45 45 45
How do I get the result?

8 comments:

Denis the SQL Menace said...

Here we go, I am sure there is a better way
I added the last 5 rows just to make sure that it is correct

create table maxnum (a int, b int, c int)

insert into maxnum values (1,2,3)
insert into maxnum values (4,3,5)
insert into maxnum values (23,44,23)
insert into maxnum values (23,45,45)
insert into maxnum values (3,2,1)
insert into maxnum values (3,1,2)
insert into maxnum values (1,3,2)
insert into maxnum values (2,1,3)
insert into maxnum values (2,3,1)

select *, case when a > b then
case when a >c then a else c end
when b > c then b else c end maxval
from maxnum

Anonymous said...

That's great, and definitely the quickest method. My first choice too. This one is interesting, as it's a lot easier to expand to more columns:

select m.a, m.b, m.c, min(vs.v), avg(vs.v), max(vs.v)
from
maxnum m
cross apply
(select m.a as v union all select m.b union all select m.c) vs
group by m.a, m.b, m.c

And of course, you could do it with unpivot too.

Anonymous said...

Actually, this one is probably best:

select a,b,c,((a+b+abs(a-b))/2+c+abs((a+b+abs(a-b))/2-c))/2 as lgst
from maxnum

This is based on the principal that abs(a-b) + a + b = twice the largest of a,b.

If you think about it, for a>b, abs(a-b)+a+b = a-b+a+b = a+a
For b>a, abs(a-b)+a+b = b-a+a+b = b+b.

So then I've extended that to include c in the calculations too.

Omnibuzz said...

Denis, you came up with the most maintainable answer we can have :)

Rob, Your first result with the cross apply was something I didn't think of (atleast not that implementation), and the second reply was more in the lines I thought of..
Here is the answer which I had in mind, just as Rob's

select a,b,c,
(
(
a + b + 2*c +
abs(a-b)
) +
abs(
a + b - 2*c + abs(a-b)
)
)/4
from maxnum

But, I do wonder that, though it feels great to give such convoluted answers with some complex logic and is perfectly fine from looking at it as a puzzle, I feel, from a manageability perspective, we are better off in following what Denis gave.

Now, from a performance perspective, is the computation better than the CASE statements?

I find that the the QEP doesn't help much in deciding which is better :)

-Omni

Hugo Kornelis said...

Hi Omnibuzz,

Denis' version, slightly optimized for readability, speen, and shorter to boot:

select *,
case when a > b and a > c then a
when b > c then b
else c end maxval
from maxnum

Omnibuzz said...

That was beautifully written Hugo. Will keep a note of this :)
-Omni

Anonymous said...

I'm curious as to why the greatest function hasn't been suggested:

Select *,greatest(a,b,c) as maxval from maxnum

Is it due to performance?

RKB said...

Really Nice Logic.........
Thanks
Rahul Bharava

Post a Comment