selecting the max value - 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)

selecting the max value

Author

Topic

bilencekic
Posting Yak Master

121 Posts

Posted - 2006-05-05 : 13:17:08

ID-s1-s2-s3-s4-s5-s6
1-54-34-22-46-78-54

do i have a chace to select the max value from here ?
or i will create a temp table and do 6 times select query and insert and selecting top value
or other chance?

MS BLESS US

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2006-05-05 : 13:25:34

use a split function from here:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=50648
and split on -
and then simply do
select max from function

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

Blog thingie: [URL="http://weblogs.sqlteam.com/mladenp"]

bilencekic
Posting Yak Master

121 Posts

Posted - 2006-05-05 : 13:33:52

no no
- isnt a chacater
that results comes from table
and s1 s2 s3 s4 re my columns
they have some values
and i want to get which column has max value
i thibk i will use temptable with 6 select queries
after
select top command

MS BLESS US

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-05-05 : 14:17:14

U better write a Function to find the max from a set of 6 values.

Then run the query as follows

Select ID, FindMax(s1,s2,s3,s4,s5) from MyTbl

A suggested function (this may be innefficient and may need some modifications)


Create Function (v1 int, v2 int, v3 int, v4 int, v5 int) returns varchar(30)
declare maxV int
declare maxVCol varchar

if	v1 > v2
	Begin
		maxV = v1
		maxVCol = s1
	End
else
	Begin
		maxV = v2
		maxVCol = s2
	End

if	v3 > maxV
	Begin
		maxV = v3
		maxVCol = s3
	End

if	v4 > maxV
	Begin
		maxV = v4
		maxVCol = s4
	End

if	v5 > maxV
	Begin
		maxV = v5
		maxVCol = s5
	End
return maxVCol

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2006-05-08 : 04:49:59

Hi all,

Here's another way - which is just like the temp table method you suggested, bilencekic, but without using a temp table...

--data
declare @t table (ID int, s1 int, s2 int, s3 int, s4 int, s5 int, s6 int)
insert @t select 1, 54, 34, 22, 46, 78, 54

--calculation
select top 1 ColumnName from (
              select 's1' as ColumnName, s1 as Value from @t
    union all select 's2', s2 from @t
    union all select 's3', s3 from @t
    union all select 's4', s4 from @t
    union all select 's5', s5 from @t
    union all select 's6', s6 from @t
) a order by Value desc

Ryan Randall
www.monsoonmalabar.com London-based IT consultancy

Solutions are easy. Understanding the problem, now, that's the hard part.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources