Max value column in a 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 2008 Forums

Transact-SQL (2008)

Max value column in a record

Author

Topic

jayram11
Yak Posting Veteran

97 Posts

Posted - 2010-06-25 : 16:07:26

Hi
i need to find the max of a value in a record which has 10 columns and put the max value in the MAX column

i have a query which does that, it gets the column with all the max values from a record but how do i insert that into the MAX column

the query is

select (select Max(DME)
from (select DME_AK as DME
union all
select DME_AL
union all
select DME_AR
union all
select DME_AZ) as DMES) as MaxDMEValue
from DME_MS_062110

say the table looks like this
A B C D E Max
1 2 3 55 7 55
4 5 12 3 8 12
2 33 4 5 66 66

any help
thnx

jayram11
Yak Posting Veteran

97 Posts

Posted - 2010-06-25 : 16:13:38

Would this work, i mean is it syntactically correct

Update DME_MS_062110
set DME_US = MaxDMEvalue from (
select (select Min(DME)
from (select DME_AK as DME
union all
select DME_AL
union all
select DME_AR
union all
select DME_AZ) as DMES) as MaxDMEValue
from DME_MS_062110) DME_MS_062110

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-06-25 : 16:19:42

SELECT w.A, w.B, w.C, w.D, w.E, g.h AS theMax
FROM YourTableNameHere AS w
CROSS APPLY (SELECT MAX(t) FROM (values(w.a), (w.b), (w.c), (w.d), (w.e)) AS a) as g(h)

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

jayram11
Yak Posting Veteran

97 Posts

Posted - 2010-06-25 : 16:42:12

Hello Peso
thnx for the reply
but i encounter the following error

Msg 170, Level 15, State 1, Line 3
Line 3: Incorrect syntax near 'APPLY'.
Msg 156, Level 15, State 1, Line 4
Incorrect syntax near the keyword 'values'.

This is my script

SELECT w.DME_AK, w.DME_AL, w.DME_AR, w.DME_AZ, w.DME_CA, g.h AS theMax
FROM DME_MS_062110 AS w
CROSS APPLY
(SELECT MAX(t) FROM (values((w.DME_AK), (w.DME_AL), (w.DME_AR), (w.DME_AZ), (w.DME_CA)) AS t)) as g(h)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-06-26 : 02:50:56

Works for me

DECLARE	@Sample TABLE
	(
		A INT,
		B INT,
		C INT,
		D INT,
		E INT
	)

INSERT	@Sample
VALUES	(1,  2,  3, 55,  7),
	(4,  5, 12,  3,  8),
	(2, 33,  4,  5, 66)

SELECT		A,
		B,
		C,
		D,
		E,
		g.h AS theMax
FROM		@Sample
CROSS APPLY	(
			SELECT	MAX(t)
			FROM	(
					VALUES	(a),
						(b),
						(c),
						(d),
						(e)
				) AS a(t)
		) AS g(h)

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

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-06-26 : 05:01:05

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=146513#575429

It seems that you are using SQL Server 2000. You should post it under a SQL 2000 forum. Posting here may get a SQL 2008 specific solution.

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

jayram11
Yak Posting Veteran

97 Posts

Posted - 2010-06-28 : 09:30:34

i think probably i know why? the database resides on sql server 2000 server and my workstation has sql server 2008

Sachin.Nand

2937 Posts

Posted - 2010-06-28 : 10:31:55

May not be the pretty but works with SQL 2000


DECLARE	@Sample TABLE
	(
		id INT identity,
		A INT,
		B INT,
		C INT,
		D INT,
		E INT
	)

INSERT	@Sample
VALUES	(1,  2,  3, 55,  7),
	(4,  5, 12,  3,  8),
	(2, 33,  4,  5, 66)

		
select * from @Sample

select S.*,T.MaxCol from
(
select MAX(col)as MaxCol,id from
(
select 
id,A
from @Sample
union  all
select 
id,B
from @Sample
union  all
select 
id,C
from @Sample
union  all
select 
id,D
from @Sample
union  all
select 
id,E
from @Sample
)T (id,col)
group by id
)T inner join @Sample S on S.id=T.id

Limitations live only in our minds. But if we use our imaginations, our possibilities become limitless.

PBUH

jayram11
Yak Posting Veteran

97 Posts

Posted - 2010-06-28 : 14:14:05

Ok forgive me for being a beginner

the declare part executes correctly

but the insert gives me an error

Msg 170, Level 15, State 1, Line 12
Line 12: Incorrect syntax near ','.

jayram11
Yak Posting Veteran

97 Posts

Posted - 2010-06-28 : 14:24:55

i think it is the compatibility level that causes the error

Sachin.Nand

2937 Posts

Posted - 2010-06-28 : 14:32:34

quote:
Originally posted by jayram11

Ok forgive me for being a beginner

the declare part executes correctly

but the insert gives me an error

Msg 170, Level 15, State 1, Line 12
Line 12: Incorrect syntax near ','.

Post the query you are using & where it is giving the error.

Limitations live only in our minds. But if we use our imaginations, our possibilities become limitless.

PBUH

jayram11
Yak Posting Veteran

97 Posts

Posted - 2010-06-28 : 15:55:20

DECLARE @Sample TABLE
(
id INT identity,
A INT,
B INT,
C INT,
D INT,
E INT
)

INSERT @Sample
VALUES (1, 2, 3, 55, 7), ----error here
(4, 5, 12, 3, 8),
(2, 33, 4, 5, 66)

Msg 170, Level 15, State 1, Line 12
Line 12: Incorrect syntax near ','.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-06-28 : 15:58:47

INSERT @Sample
SELECT 1, 2, 3, 55, 7 UNION ALL
SELECT 4, 5, 12, 3, 8 UNION ALL
SELECT 2, 33, 4, 5, 66

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources