middle of three numbers - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

middle of three numbers

Author

Topic

rohitkumar
Constraint Violating Yak Guru

472 Posts

Posted - 2010-11-16 : 09:35:06

I have three columns with positive integers in them, whats a good way to get the middle number?

for eg.


declare @temp table (num1 int, num2 int, num3 int)

insert into @temp (num1, num2, num3)
select 1,4,8
union all
select 3,9,2
union all
select 3,3,11

select * from @temp

output for above should be

4
3
3

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-11-16 : 09:39:47

Shouldn't it be
4
3
3
????

No, you're never too old to Yak'n'Roll if you're too young to die.

TimSman
Posting Yak Master

127 Posts

Posted - 2010-11-16 : 09:39:52

[code]
SElECT
num2
FROM
@temp
[/code]

rohitkumar
Constraint Violating Yak Guru

472 Posts

Posted - 2010-11-16 : 09:55:37

quote:
Originally posted by webfred

Shouldn't it be
4
3
3
????

No, you're never too old to Yak'n'Roll if you're too young to die.

my mistake, yes it should be

4
3
3

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2010-11-16 : 11:49:37

[CODE]select case
when num1 >= num2 and num1 <=num3 then num1
when num1 <= num2 and num1 >=num3 then num1
when num2 >= num1 and num2 <=num3 then num2
when num2 <= num1 and num2 >=num3 then num2
else num3
end
from @temp[/CODE]

=======================================
In all life one should comfort the afflicted, but verily, also, one should afflict the comfortable, and especially when they are comfortably, contentedly, even happily wrong. -John Kenneth Galbraith, economist (1908-2006)

Sachin.Nand

2937 Posts

Posted - 2010-11-16 : 14:43:16

Below query assumes that you have a surrogate key to identify each record


declare @temp table (id int identity,num1 int, num2 int, num3 int)

insert into @temp (num1, num2, num3)
select 1,4,8
union all
select 3,9,2
union all
select 3,3,11
union all
select 13,23,15

select id,col from 
(
select *,ROW_NUMBER()over(partition by id order by col)rid from
(select * from @temp)u
unpivot
(col for columns in(num1, num2, num3))v
)T where rid=2

PBUH

jcelko
Esteemed SQL Purist

547 Posts

Posted - 2010-11-16 : 15:09:27

Tables need keys and constraints

CREATE TABLE ThreeInts
(num1 INTEGER NOT NULL,
num2 INTEGER NOT NULL,
num3 INTEGER NOT NULL,
PRIMARY KEY (num1, num2, num3));
GO

INSERT INTO ThreeInts (num1, num2, num3)
VALUES (1, 4, 8), (3, 9, 2), (3, 3, 11);
GO

SELECT
CASE
WHEN num2 BETWEEN num1 AND num3
OR num2 BETWEEN num3 AND num1
THEN num2

WHEN num1 BETWEEN num2 AND num3
OR num1 BETWEEN num3 AND num2
THEN num1

WHEN num3 BETWEEN num1 AND num2
OR num3 BETWEEN num2 AND num1
THEN num3
END
FROM ThreeInts ;

--CELKO--
Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL

rohitkumar
Constraint Violating Yak Guru

472 Posts

Posted - 2010-11-16 : 15:56:16

wanted to avoid ROW_NUMBER() as this goes in a function which is being called for millions of rows.

went ahead with Bustaz and Celko's suggestion

thank for the help.

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2010-11-17 : 09:07:30

Millions of rows?
Pshaw!
I liked Sachin's method best, but just for fun here is another.
Try them all out, and let us know how the performance compares.


declare @temp table (PK Char(1), num1 int, num2 int, num3 int)

insert into @temp (PK, num1, num2, num3)
select 'a', 1,4,8
union all
select 'b', 3,9,2
union all
select 'c', 3,3,11

select	PK, SUM(num) - MIN(num) - MAX(num) as Middle
from	--Normalized
		(select PK, num1 as num from @temp
		union all
		select PK, num2 as num from @temp
		union all
		select PK, num3 as num from @temp) Normalized
group by PK

________________________________________________
If it is not practically useful, then it is practically useless.
________________________________________________

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-11-17 : 10:24:54

quote:
Originally posted by blindman

Millions of rows?
Pshaw!
I liked Sachin's method best, but just for fun here is another.
Try them all out, and let us know how the performance compares.

declare @temp table (PK Char(1), num1 int, num2 int, num3 int)

insert into @temp (PK, num1, num2, num3)
select 'a', 1,4,8
union all
select 'b', 3,9,2
union all
select 'c', 3,3,11

select PK, SUM(num) - MIN(num) - MAX(num) as Middle
from --Normalized
(select PK, num1 as num from @temp
union all
select PK, num2 as num from @temp
union all
select PK, num3 as num from @temp) Normalized
group by PK

________________________________________________
If it is not practically useful, then it is practically useless.
________________________________________________

Looks soooooo beautyful to me

No, you're never too old to Yak'n'Roll if you're too young to die.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources