Select the max value of multiple columns

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 the max value of multiple columns

Author

Topic

mavershang
Posting Yak Master

111 Posts

Posted - 2009-02-19 : 18:14:10

Hi all. My question is about to select the max value of multiple columns.

For instance,
the table is like
col1 col2 col3 col4 col5
aa 1 2 4 6
bb 8 7 1 3
cc 11 3 1 10
...

The output I want is like
-------------------
aa 6
bb 8
cc 11
....
--------------------
which is for each row, the max value of col2-col5 together with the value of col1.

Thanks.

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-02-19 : 18:27:59

[code]Select col1,Max(col2) from
(
Select col1,col2 from Table
union all
Select col1,col3 from Table
union all
Select col1,col4 from Table
union all
Select col1,col5 from Table)Z
Group by col1
Order by col1[/code]

Rajesh Jonnalagadda
Starting Member

45 Posts

Posted - 2009-02-20 : 08:11:23

Try this,
CREATE TABLE #TEST
(
col1 NVARCHAR(50)
,col2 INT
,col3 INT
,col4 INT
,col5 INT
)
INSERT INTO #TEST
SELECT 'aa', 1, 2, 4, 6 UNION ALL
SELECT 'bb', 8, 7, 1, 3 UNION ALL
SELECT 'cc', 11, 3, 1, 10
SELECT col1,
CASE WHEN col2 > col3 THEN col2
WHEN col3 > col4 THEN col3
WHEN col4 > col5 THEN col4
ELSE Col5 END AS Result
FROM #TEST

Rajesh Jonnalagadda
[url="http://www.ggktech.com
"]GGK TECH[/url]

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-02-20 : 08:19:03

quote:
Originally posted by Rajesh Jonnalagadda

Try this,
CREATE TABLE #TEST
(
col1 NVARCHAR(50)
,col2 INT
,col3 INT
,col4 INT
,col5 INT
)
INSERT INTO #TEST
SELECT 'aa', 1, 2, 4, 6 UNION ALL
SELECT 'bb', 8, 7, 1, 3 UNION ALL
SELECT 'cc', 11, 3, 1, 10 UNION ALL

SELECT col1,
CASE WHEN col2 > col3 THEN col2
WHEN col3 > col4 THEN col3
WHEN col4 > col5 THEN col4
ELSE Col5 END AS Result
FROM #TEST

Rajesh Jonnalagadda
[url="http://www.ggktech.com
"]GGK TECH[/url]

Wouldn't work.

Rajesh Jonnalagadda
Starting Member

45 Posts

Posted - 2009-02-20 : 08:33:31

Hi sakets_2000,

Thanks sakets.

I have edited my post. check it now

Rajesh Jonnalagadda
[url="http://www.ggktech.com
"]GGK TECH[/url]

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-02-20 : 08:34:39

quote:
Originally posted by Rajesh Jonnalagadda

Hi sakets_2000,

Thanks sakets.

I have edited my post. check it now

Rajesh Jonnalagadda
[url="http://www.ggktech.com
"]GGK TECH[/url]

Try with this,

INSERT INTO #TEST
SELECT 'aa', 10, 2, 4, 600 UNION ALL
SELECT 'bb', 800, 7, 1, 3000 UNION ALL
SELECT 'cc', 11, 3, 1, 1000

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-20 : 09:37:18

[code]SELECT col1,MAX(Val)
FROM
(
SELECT col1, col2, col3, col4, col5
FROM Table
)m
UNPIVOT (Val FOR ColCat IN ([col2],[col3],[col4],[col5]))u
GROUP BY col1
[/code]

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-02-20 : 09:52:49

quote:
Originally posted by sakets_2000

quote:
Originally posted by Rajesh Jonnalagadda

Try this,
CREATE TABLE #TEST
(
col1 NVARCHAR(50)
,col2 INT
,col3 INT
,col4 INT
,col5 INT
)
INSERT INTO #TEST
SELECT 'aa', 1, 2, 4, 6 UNION ALL
SELECT 'bb', 8, 7, 1, 3 UNION ALL
SELECT 'cc', 11, 3, 1, 10 UNION ALL

SELECT col1,
CASE WHEN col2 > col3 THEN col2
WHEN col3 > col4 THEN col3
WHEN col4 > col5 THEN col4
ELSE Col5 END AS Result
FROM #TEST

Rajesh Jonnalagadda
[url="http://www.ggktech.com
"]GGK TECH[/url]

Wouldn't work.

Try Like This:

SELECT col1, 
Case When Col2>Col3 and Col2>Col4 and Col2>Col5 then Col2
When Col3>Col2 and Col3>Col4 and Col3>Col5 then Col3
When Col4>Col2 and Col4>Col3 and Col4>Col5 then Col4
Else Col5 End
FROM #TEST

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-02-20 : 15:03:33

quote:
Originally posted by sodeep

quote:
Originally posted by sakets_2000

quote:
Originally posted by Rajesh Jonnalagadda

Try this,
CREATE TABLE #TEST
(
col1 NVARCHAR(50)
,col2 INT
,col3 INT
,col4 INT
,col5 INT
)
INSERT INTO #TEST
SELECT 'aa', 1, 2, 4, 6 UNION ALL
SELECT 'bb', 8, 7, 1, 3 UNION ALL
SELECT 'cc', 11, 3, 1, 10 UNION ALL

SELECT col1,
CASE WHEN col2 > col3 THEN col2
WHEN col3 > col4 THEN col3
WHEN col4 > col5 THEN col4
ELSE Col5 END AS Result
FROM #TEST

Rajesh Jonnalagadda
[url="http://www.ggktech.com
"]GGK TECH[/url]

Wouldn't work.

Try Like This:

SELECT col1,
Case When Col2>Col3 and Col2>Col4 and Col2>Col5 then Col2
When Col3>Col2 and Col3>Col4 and Col3>Col5 then Col3
When Col4>Col2 and Col4>Col3 and Col4>Col5 then Col4
Else Col5 End
FROM #TEST

You might want to test with this,

INSERT INTO #TEST
SELECT 'aa', 10, 10, 10, 2 UNION ALL
SELECT 'bb', 10, 10, 10, 4 UNION ALL
SELECT 'cc', 10, 10, 10, 6

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-02-20 : 15:16:02

Then change it like this:

SELECT col1,
Case When Col2>=Col3 and Col2>=Col4 and Col2>=Col5 then Col2
When Col3>=Col2 and Col3>=Col4 and Col3>=Col5 then Col3
When Col4>=Col2 and Col4>=Col3 and Col4>=Col5 then Col4
Else Col5 End
FROM #TEST

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-02-20 : 15:26:26

quote:

Originally posted by visakh16

SELECT col1,MAX(Val)
FROM
(
SELECT col1, col2, col3, col4, col5
FROM Table
)m
UNPIVOT (Val FOR ColCat IN ([col2],[col3],[col4],[col5]))u
GROUP BY col1

I like Visakh's unpivot solution - but it could be slightly simpler:


select col1, max(val)
from   #test d
unpivot (val for Cols in ([col2],[col3],[col4],[col5])) u
group by col1

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources