Set a derived field returning lowest value

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)

Set a derived field returning lowest value

Author

Topic

DLTaylor
Posting Yak Master

136 Posts

Posted - 2009-03-27 : 09:30:00

Within SQL 2000 I have a table (which I’ve simplified - see SQL below).
The last column ‘lowest number’ is intended to be derived field that contains the lowest number from the fields beginning with f.
Can anyone advise on the best way to set the ‘lowest number’ (or in this case nvarchar

)

Any help would be much appreciated
Thanks

IF OBJECT_ID( N'data', N'U') IS NOT NULL
drop table [dbo].[data]

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[data](
[f1] [nvarchar](1) null,
[f2] [nvarchar](1) null,
[f3] [nvarchar](1) null,
[f4] [nvarchar](1) null,
[f5] [nvarchar](1) null,
[lowestNumber] [nvarchar](1) null
) ON [PRIMARY]

Insert into data
select '2','1','2','4','7',null UNION all
select '3','7','9',null,null,null UNION all
select '8','2',null,null,null,null UNION all
select '2','1',null,null,null,null UNION all
select '9','1','2','1','7',null

select * from data

IF OBJECT_ID( N'data', N'U') IS NOT NULL
drop table [dbo].[data]

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-03-27 : 09:46:01

This should do


case 
	when [f1]*1<[f2] and [f1]*1<[f3] and [f1]*1<[f4] and [f1]*1<[f5] then [f1]
	when [f2]*1<[f3] and [f2]*1<[f4] and [f2]*1<[f5] then [f2]
	when [f3]*1<[f4] and [f3]*1<[f5] then [f3]
	when [f4]*1<[f5] then [f4]
	else [f5]
end

But you need to use proper int datatype for the columns

Madhivanan

Failing to plan is Planning to fail

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-03-27 : 09:57:32

Madhivanan, That might now work in all cases. e.g. 10,10,1,1,null

Dan, You should give this a read.
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=86906

DLTaylor
Posting Yak Master

136 Posts

Posted - 2009-03-27 : 11:04:02

thank you both for your help.
Madhivanan; i tried yours but it returned NULLs when the colums included nulls.
(see sql below)
Thanks sakets - i will uses youyr link and im sure i will get it working.

IF OBJECT_ID( N'data', N'U') IS NOT NULL
drop table [dbo].[data]

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[data](
[f1] [int] NULL,
[f2] [int] NULL,
[f3] [int] NULL,
[f4] [int] NULL,
[f5] [int] NULL,
[lowestNumber] [nvarchar](1) null
) ON [PRIMARY]

Insert into data
select '2','1','2','4','7',null UNION all
select '3','7','9',null,null,null UNION all
select '8','2',null,null,null,null UNION all
select '2','1',null,null,null,null UNION all
select '9','1','2','1','7',null

update data
set
lowestNumber =
case
when [f1]*1<[f2] and [f1]*1<[f3] and [f1]*1<[f4] and [f1]*1<[f5] then [f1]
when [f2]*1<[f3] and [f2]*1<[f4] and [f2]*1<[f5] then [f2]
when [f3]*1<[f4] and [f3]*1<[f5] then [f3]
when [f4]*1<[f5] then [f4]
else [f5] end

select * from data

IF OBJECT_ID( N'data', N'U') IS NOT NULL
drop table [dbo].[data]

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-03-27 : 11:08:03

[code]UPDATE Data
SET LowestNumber = f1

UPDATE Data
SET LowestNumber = f2
WHERE 1.0E * f2 < LowestNumber

UPDATE Data
SET LowestNumber = f3
WHERE 1.0E * f3 < LowestNumber

UPDATE Data
SET LowestNumber = f4
WHERE 1.0E * f4 < LowestNumber

UPDATE Data
SET LowestNumber = f5
WHERE 1.0E * f5 < LowestNumber

SELECT *
FROM Data[/code]

E 12°55'05.63"
N 56°04'39.26"

DLTaylor
Posting Yak Master

136 Posts

Posted - 2009-03-27 : 11:32:10

Good idea peso.
As this is the easiest for me to understand \ apply; I will adopt.
Thanks very much

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources