Max Value within a row - 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 2000 Forums

Transact-SQL (2000)

Max Value within a row

Author

Topic

ceri1
Starting Member

2 Posts

Posted - 2006-11-17 : 10:41:50

Hi, basically i have a statement that uses a series of Sum(case WHEN column a = 1 Then item_qty) to build up a horizontal list like the one below

cluster_id, district_id, output one, output two, output three etc
12 8 4 9 4
13 10 1 2 3

Is there a function that will look along myrecord and find the maximum value of my columns e.g 9 for cluster 12 and 3 for 13.
I need to do it in this transposed format as i'm trying to replicate a SAS based method
Thanks

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2006-11-17 : 11:05:06

there is no such function.

do you have a variable or constant number of columns?

Go with the flow & have fun! Else fight the flow

blog thingie: http://weblogs.sqlteam.com/mladenp

ceri1
Starting Member

2 Posts

Posted - 2006-11-17 : 11:14:32

A static number of columns, 10 in all in fact some having the value of 0

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2006-11-17 : 12:12:14

this was fun


create table #t1
(c_id int, d_id int, o1 int, o2 int, o3 int)

insert into #t1
select 12, 8, 4, 9, 4 union all
select 13, 10, 1, 2, 3 union all
select 14, 1, 2, 2, 5 union all
select 15, 1, 1, 1, 1 union all
select 16, 82, 24, 19, 44 


SELECT * FROM #t1 

declare @sql varchar(8000)
select	@sql = 'select c_id, max(maxVal) as maxInRow from ('

SELECT	@sql = @sql + 'select c_id, ' + column_name + ' as maxVal from #t1 where c_id=''<idValue>'' union all '
FROM	tempdb.information_schema.columns
where	table_name like '#t1%'
	and column_name not in ('c_id', 'd_id')

select @sql = @sql + ' select 0, '''') t1 where c_id <> '''' group by c_id'

declare @sql1 nvarchar(4000)
select @sql1 = ''
select @sql1 = @sql1 + sqlText + ' union all '
from
(
select replace(@sql, '<idValue>', c_id) as sqlText
from #t1
) t1
select @sql1  = left(@sql1 , len(@sql1 )-9)
--select @sql1 
exec(@sql1)

drop table #t1

Go with the flow & have fun! Else fight the flow

blog thingie: http://weblogs.sqlteam.com/mladenp

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2006-11-17 : 12:24:15

or with the help of a function and little less complex:



create function getMaxValue(@val1 int, @val2 int)
returns int
as
begin
	return case when @val1 > @val2 then @val1 else @val2 end
end
go

create table #t1
(c_id int, d_id int, o1 int, o2 int, o3 int)

insert into #t1
select 12, 8, 4, 9, 4 union all
select 13, 10, 1, 2, 3 union all
select 14, 1, 2, 2, 5 union all
select 15, 1, 1, 1, 1 union all
select 16, 82, 24, 19, 44 

SELECT * FROM #t1 

select c_id, dbo.getMaxValue(dbo.getMaxValue(o1, o2), o3) as MaxValue -- add mor nested ones here
from #t1

drop table #t1

Go with the flow & have fun! Else fight the flow

blog thingie: http://weblogs.sqlteam.com/mladenp

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2006-11-17 : 12:26:43

create function MaxFrom10(@Inp1 int, @Inp2 int, @Inp3 int, @Inp4 int, @Inp5 int, 
    @Inp6 int, @Inp7 int, @Inp8 int, @Inp9 int, @Inp10 int)
returns int
as
begin
	declare @max int
	select @max = -2147483648
	if @Inp1 > @max set @max = @Inp1
	if @Inp2 > @max set @max = @Inp2
	if @Inp3 > @max set @max = @Inp3
	if @Inp4 > @max set @max = @Inp4
	if @Inp5 > @max set @max = @Inp5
	if @Inp6 > @max set @max = @Inp6
	if @Inp7 > @max set @max = @Inp7
	if @Inp8 > @max set @max = @Inp8
	if @Inp9 > @max set @max = @Inp9
	if @Inp10 > @max set @max = @Inp10
	return @max
end

Then

create table #t1
(c_id int, d_id int, o1 int, o2 int, o3 int)

insert into #t1
select 12, 8, 4, 9, 4 union all
select 13, 10, 1, 2, 3 union all
select 14, 1, 2, 2, 5 union all
select 15, 1, 1, 1, 1 union all
select 16, 82, 24, 19, 44 

SELECT c_id, d_id, dbo.MaxFrom10(o1, o2, o3, 0, 0, 0, 0, 0, 0, 0) AS MaxOutput FROM #t1 

drop table #t1

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2006-11-17 : 12:29:20

i just love to complicate a problem...
a simpler solutions seems to come easier that way

Go with the flow & have fun! Else fight the flow

blog thingie: http://weblogs.sqlteam.com/mladenp

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources