Any quick way to find middle value out of 3?

Please help
declare @.t table (id1 int not null, id2 int not null, id3 int null)
insert @.t values (1,2,null) -- middle null
insert @.t values (10,20,1) -- middle 10
insert @.t values (11,23,12) -- middle 12
insert @.t values (20,100,123) -- middle 100
select *, middle(ID1,id2,id3) --< middle out of 3
FROM @.t
Thank you!There are probably better ways, but this should work.
You could do the same thing using BETWEEN but I prefer to avoid using
BETWEEN.
I assumed that the values could be the same between 2 or 3 of the columns
SELECT ID1, ID2, ID3,
CASE WHEN ID1 >= ID2 AND ID1 <=ID3 THEN ID1
WHEN ID1 >= ID3 AND ID1 <=ID2 THEN ID1
WHEN ID2 >= ID1 AND ID2 <=ID3 THEN ID2
WHEN ID2 >= ID3 AND ID2 <=ID1 THEN ID2
ELSE ID3 END as Middle
FROM @.t
--
Ryan Powers
Clarity Consulting
http://www.claritycon.com
"Farmer" wrote:

> Please help
> declare @.t table (id1 int not null, id2 int not null, id3 int null)
> insert @.t values (1,2,null) -- middle null
> insert @.t values (10,20,1) -- middle 10
> insert @.t values (11,23,12) -- middle 12
> insert @.t values (20,100,123) -- middle 100
>
> select *, middle(ID1,id2,id3) --< middle out of 3
> FROM @.t
>
> Thank you!
>
>|||One additional note, this would get a bit uglier if id1 or id2 could also be
null. I was able to cheat a bit since only id3 was allowed to be null, and
you seemed to want the null value for your middle value.
HTH
--
Ryan Powers
Clarity Consulting
http://www.claritycon.com
"Ryan Powers" wrote:
> There are probably better ways, but this should work.
> You could do the same thing using BETWEEN but I prefer to avoid using
> BETWEEN.
> I assumed that the values could be the same between 2 or 3 of the columns
> SELECT ID1, ID2, ID3,
> CASE WHEN ID1 >= ID2 AND ID1 <=ID3 THEN ID1
> WHEN ID1 >= ID3 AND ID1 <=ID2 THEN ID1
> WHEN ID2 >= ID1 AND ID2 <=ID3 THEN ID2
> WHEN ID2 >= ID3 AND ID2 <=ID1 THEN ID2
> ELSE ID3 END as Middle
> FROM @.t
> --
> Ryan Powers
> Clarity Consulting
> http://www.claritycon.com
>
> "Farmer" wrote:
>|||using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
public partial class UserDefinedFunctions
{
[Microsoft.SqlServer.Server.SqlFunction]
public static SqlInt32 Middle(SqlInt32 i1, SqlInt32 i2, SqlInt32 i3)
{
if (i1 < i2)
{
if (i3 < i1)
return i1;
if (i3 > i2)
return i2;
return i3;
}
if (i1 > i2)
{
if (i3 > i1)
return i1;
if (i3 < i2)
return i2;
return i3;
}
if (i3 <= i1)
return i1;
return i2;
}
};
William Stacey [MVP]
"Farmer" <someone@.somewhere.com> wrote in message
news:eFURSHkFGHA.1816@.TK2MSFTNGP11.phx.gbl...
> Please help
> declare @.t table (id1 int not null, id2 int not null, id3 int null)
> insert @.t values (1,2,null) -- middle null
> insert @.t values (10,20,1) -- middle 10
> insert @.t values (11,23,12) -- middle 12
> insert @.t values (20,100,123) -- middle 100
>
> select *, middle(ID1,id2,id3) --< middle out of 3
> FROM @.t
>
> Thank you!
>