A Scenario to Ponder #3
Now this is pretty similar to the previous scenario (#2) , but a bit more interesting, I hope.
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 intermediate (neither max nor min) value of a,b,c. So, the result will be this way:
a b c intermed
1 2 3 2
4 3 5 4
23 44 23 23
23 45 45 45
How do I get the result?
Take it, that all three values (a,b,c) in the row cannot be the same.
Posts
9 comments:
here is a kludge
select a,b,c,case when a > b and a < c then a
when b > a and b < c then b
when c < b or c > a then c end
from maxnum
Though that gets close to the answer, it might fail if a < b and a > c, then you still need to return a, right?
-Omni
Borrowing Denis' extended test data from the previous scenario to ponder, I arrived at
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 and a > c
then case when b > c then b else c end
when a > b or a > c then a
when c > b then b else c end maxval
from maxnum
But I'm sure that there's got to be a better way through a mathemetical approach.
That was too good Hugo. It took me a while to understand the logic. A humbling solution indeed. And as you guessed, there is a mathematical approach. Let me know your thoughts.
-Omni
Hi all,
Here is my solution. I am not sure if this is the best approach. Let me know your thoughts.
select a,b,c,
(abs((sign(a-b)- sign(a-c)))*a +
abs((sign(b-c)-sign(b-a)))*b +
abs((sign(c-a) - sign(c-b)))*c)/2
from maxnum
And before you retaliate, this solution will not work if all three columns are equal. Anyone out there who can get a mathematical approach to work in that case too?
Sorry I'm late guys... how about subtracting the min and max from the total:
select *,
a+b+c
- case when a>b and a>c then a when b>c then b else c end
- case when a<b and a<c then a when b<c then b else c end
from maxnum
That was a good one Rob. I mean, really unconventional :)
-Omni
Thanks. Pleased you liked it. And I think it's simpler.
I've done a bunch of playing around with ideas for getting medians - there's no nice way to do it. But if you're dealing with only a few numbers, then just removing the top and bottom can really help. :)
what about this?
SELECT a,b,c,
CASE WHEN AB = AC
THEN CASE WHEN AB > BC THEN BC ELSE AC END
ELSE CASE WHEN AB > AC THEN AC ELSE AB END END
FROM
(select a,b,c,
CASE WHEN A>B Then A ELSE B END AB,
CASE WHEN A>C THEN A ELSE C END AC,
CASE WHEN B>C THEN B ELSE C END BC
from maxnum) A
Post a Comment