Select max value from multiple column

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)

Select max value from multiple column

Author

Topic

juicyapple
Posting Yak Master

176 Posts

Posted - 2008-07-10 : 03:27:56

Hi, how to select max value from multiple columns in one table?

ssnaik84
Starting Member

15 Posts

Posted - 2008-07-10 : 03:35:37

Can u bit explain table structure?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-07-10 : 03:37:40

write a udf to do it. Pass in the columns into the UDF and return the max value

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

juicyapple
Posting Yak Master

176 Posts

Posted - 2008-07-10 : 03:43:55

for eg.

Create tableA
(SE1Top int,
SE1Btm int,
SE2Top int,
SE2Btm int,
SE3Top int,
SE3Btm int)

I want to retrieve the max value from all the 6 columns. Please advise. Thanks.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-10 : 03:55:48

do you mean max of each column or max value of all the columns?
if former use,

SELECT MAX(SE1Top),
MAX(SE1Btm),
MAX(SE2Top),
MAX(SE2Btm),
MAX(SE3Top),
MAX(SE3Btm)
FROM tableA

if latter,

SELECT MAX(t.val)
FROM
(SELECT SE1Top AS Val
 FROM tableA
UNION ALL
SELECT SE1Btm AS Val
 FROM tableA
UNION ALL
SELECT SE2Top AS Val
 FROM tableA
UNION ALL
SELECT SE2Btm AS Val
 FROM tableA
UNION ALL
SELECT SE3Top AS Val
 FROM tableA
UNION ALL
SELECT SE3Btm AS Val
 FROM tableA
)t

ssnaik84
Starting Member

15 Posts

Posted - 2008-07-10 : 04:03:22

Proceed in this way.. for 6 cols..

select
maxval = case
when col_1 > col_2 and col_1 > col_3 then col_1
when col_2 > col_1 and col_2 > col_3 then col_2
else col_3
end
from
tableA

juicyapple
Posting Yak Master

176 Posts

Posted - 2008-07-10 : 04:31:40

quote:

Originally posted by visakh16

do you mean max of each column or max value of all the columns?
if former use,

SELECT MAX(SE1Top),
MAX(SE1Btm),
MAX(SE2Top),
MAX(SE2Btm),
MAX(SE3Top),
MAX(SE3Btm)
FROM tableA

if latter,

SELECT MAX(t.val)
FROM
(SELECT SE1Top AS Val
 FROM tableA
UNION ALL
SELECT SE1Btm AS Val
 FROM tableA
UNION ALL
SELECT SE2Top AS Val
 FROM tableA
UNION ALL
SELECT SE2Btm AS Val
 FROM tableA
UNION ALL
SELECT SE3Top AS Val
 FROM tableA
UNION ALL
SELECT SE3Btm AS Val
 FROM tableA
)t

Thanks for the reply. But I wonder that using 'union' would have performance issue?

bjoerns
Posting Yak Master

154 Posts

Posted - 2008-07-10 : 12:12:48

With UNION, yes. That's why Visakh uses UNION ALL.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources