### 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.

## 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