Selecting the smallest of different values

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 2000 Forums

Transact-SQL (2000)

Selecting the smallest of different values

Author

Topic

cschl02
Starting Member

3 Posts

Posted - 2006-11-30 : 23:20:02

Hello all,

I've got a DB containing multiple tables, each of which has an identically named time field (e.g. tbl1.val, tbl2.val, tbl3.val). Occasionally one or more of these nulls is/are a null value. I need to select the smallest non-null value for insertion into another table. So, for example, if I had:

tbl1.val= '1/2/3 3pm'
tbl2.val = '1/2/3 4pm'
tbl3.val = '1/2/3 2pm'

..the query would select '1/2/3 2pm'. Or, if I had:

tbl1.val = NULL
tbl2.value = '1/2/3 5pm'
tbl3.value = '1/2/3 2pm'

..I'd get '1/2/3 2pm'. One way I could see doing this is by

CASE lowesttime
when tbl1.val < tbl2.val and tbl1.val < tbl3.val then tbl1.val
when tbl2.val < tbl1.val and tbl2.val < tbl3.val then tbl2.val
else tbl3.val

However, this doesn't seem to handle null values too well. More importantly, it's klunky and doesn't scale too well. I can't help but think there's a very simple/elegant way to do this. Can anyone help?

I sincerely appreciate the help (which, of course, I wouldn't have to ask for if I had the experience most people here do).

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-12-01 : 00:02:22

How about using UNION:

SELECT MIN(VAL)
FROM
(
	SELECT VAL FROM TBL1 UNION ALL
	SELECT VAL FROM TBL2 UNION ALL
	SELECT VAL FROM TBL3
) T

Harsh Athalye
India.
"Nothing is Impossible"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-12-01 : 02:53:37

Does Val column have varchar or DATETIME datatype?

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources