Maximum within record - 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)

Maximum within record

Author

Topic

sadderson
Starting Member

13 Posts

Posted - 2009-06-21 : 00:35:09

I have three fields in a record

ID - One - Two - Three

1 - A - Null - Null
2 - B - A - Null
3 - C - B - A
4 - B - A - Null
5 - D - Null - Null
6 - E - D - Null

I need to find A in all for all of these records

So that it can be represented

1 - A
2 - A
3 - A
4 - A
5 - D
6 - D

Any Idea's

Thanks

Scott

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-06-21 : 00:52:22

write your own function to get the maximum

create function fn_max_3val
(
	@val1	varchar(10),
	@val2	varchar(10),
	@val3	varchar(10)
)
returns	varchar(10)
as
begin
	return
	(
		select	min(val)
		from
		(
			select	val = @val1 union all
			select	val = @val2 union all
			select	val = @val3
		) a
	)
end

KH
[spoiler]Time is always against us[/spoiler]

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-06-21 : 00:54:30

[code]
DECLARE @TAB TABLE (ID INT, ONE CHAR(1), TWO CHAR(1), THREE CHAR(1))
INSERT INTO @TAB SELECT 1 , 'A' , NULL , NULL UNION ALL SELECT
2 , 'B' , 'A' , NULL UNION ALL SELECT
3 , 'C' , 'B' , 'A' UNION ALL SELECT
4 , 'B' , 'A' , NULL UNION ALL SELECT
5 , 'D' , NULL , NULL UNION ALL SELECT
6 , 'E' , 'D' , NULL

SELECT ID,COALESCE(THREE,TWO,ONE) FROM @TAB
[/code]

sadderson
Starting Member

13 Posts

Posted - 2009-06-21 : 01:03:50

Thanks for all the responses...

BKLR your solution was the easiest and works for my situation..

Thanks so much..

Scott

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-06-21 : 01:17:36

welcome

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources