Oct 14, 2006

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:

Denis the SQL Menace said...

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

Omnibuzz said...

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

Hugo Kornelis said...

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.

Omnibuzz said...

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

Omnibuzz said...

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?

Anonymous said...

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

Omnibuzz said...

That was a good one Rob. I mean, really unconventional :)

-Omni

Rob Farley said...

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. :)

RandomJoblessGuy said...

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