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
1 2 3 3
4 3 5 5
23 44 23 44
23 45 45 45
How do I get the result?
Posts
8 comments:
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
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.
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.
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
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
That was beautifully written Hugo. Will keep a note of this :)
-Omni
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?
Really Nice Logic.........
Thanks
Rahul Bharava
Post a Comment