Column value with min Type - 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

General SQL Server Forums

New to SQL Server Programming

Column value with min Type

Author

Topic

abc123
Starting Member

47 Posts

Posted - 2009-03-09 : 06:09:30

I have one table with following data

ID Type1 Type2 Type3 Type4 SystemSelectedType
1 1.111 2.33 2.31 9.01 Type1
2 4.5 3.7 4.9 10.8 Type2
3 3.1 2.3 9.6 1.2 Type4

I want to SystemSelectedType whose Type1,Type2,Type3 and Type4 has minimum value.

How I can update/get SystemSelectedType with min of Type1,Type2,Type3 and Type4?

pootle_flump

1064 Posts

Posted - 2009-03-09 : 06:16:44

Hi

If your table was *normalised* this would be really, really easy:
http://www.tonymarston.net/php-mysql/database-design.html

Otherwise something like:

SELECT min_type = CASE WHEN Type1 <= Type2 AND Type1 <= Type3 AND Type1 <= Type3 THEN Type1 WHEN Type2 <= Type1 AND Type2 <= Type3 AND Type2 <= Type3 THE Type2
.....
END

abc123
Starting Member

47 Posts

Posted - 2009-03-09 : 06:35:16

I have tried Case query but every time it is displaying SystemSelectedType = NULL

abc123
Starting Member

47 Posts

Posted - 2009-03-09 : 08:25:23

now I am getting data.But in SystemSelectedType column I want Column name (like Type1,Type2) not value (1.111 ). How I can display Column Name (like Type1,Type2) in SystemSelectedType column ?

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-03-09 : 08:28:06

try this once
declare @t table(ID int, Type1 decimal(18,3), Type2 decimal(18,3), Type3 decimal(18,3), Type4 decimal(18,3))
insert into @t select 1, 1.111, 2.33, 2.31, 9.01
insert into @t select 2, 4.5, 3.7, 4.9, 10.8
insert into @t select 3, 3.1, 2.3, 9.6, 1.2

select *, case when Type1 <= Type2 AND Type1 <= Type3 AND Type1 <= Type4 THEN 'Type1'
when Type2 <= Type1 AND Type2 <= Type3 AND Type2 <= Type4 THEN 'Type2'
when Type3 <= Type1 AND Type3 <= Type2 AND Type3 <= Type4 THEN 'Type3'
ELSE 'Type4'
end as SystemSelectedType from @t

pootle_flump

1064 Posts

Posted - 2009-03-09 : 08:35:49

quote:
Originally posted by abc123

now I am getting data.But in SystemSelectedType column I want Column name (like Type1,Type2) not value (1.111 ). How I can display Column Name (like Type1,Type2) in SystemSelectedType column ?

Once again this is trivial if your table is designed correctly.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-03-10 : 02:43:58

[code]
UPDATE t
SET t.SystemSelectedType=r.TypeVal
FROM Table t
JOIN(
SELECT ROW_NUMBER() OVER (PARTITION BY ID ORDER BY Val) AS Seq,
ID,TypeVal,Val
FROM Table t
UNPIVOT (Val FOR TypeVal IN ([Type1],[Type2],[Type3],[Type4]))u
)r
ON r.ID=t.ID
AND r.Seq=1
[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources