finding max value among 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 2000 Forums

SQL Server Development (2000)

finding max value among columns

Author

Topic

sanjnep
Posting Yak Master

191 Posts

Posted - 2008-05-14 : 13:10:13

Is there any function in SQL to compare largest value among three columns?

dt1 dt2 dt3 maxval (I need this output)
.................................
1 3 4 4
4 4 5 5
6 6 3 6

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-05-14 : 13:36:07

[code]
DECLARE @table1 table (dt1 tinyint, dt2 tinyint, dt3 tinyint)

INSERT INTO @table1 VALUES(1,3,4)
INSERT INTO @table1 VALUES(4,4,5)
INSERT INTO @table1 VALUES(6,6,3)

SELECT
dt1, dt2, dt3,
maxval =
CASE
WHEN dt1 >= dt2 AND dt1 >= dt3 THEN dt1
WHEN dt2 >= dt1 AND dt2 >= dt3 THEN dt2
WHEN dt3 >= dt1 AND dt3 >= dt2 THEN dt3
END
FROM @table1
[/code]

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Database maintenance routines:
http://weblogs.sqlteam.com/tarad/archive/2004/07/02/1705.aspx

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-14 : 13:39:37

DO you have a primary key in your table?

sanjnep
Posting Yak Master

191 Posts

Posted - 2008-05-14 : 14:18:12

Thanks a lot Tara Kizer

Sanjeev Shrestha
12/17/1971

johnsql
Posting Yak Master

161 Posts

Posted - 2008-05-28 : 14:36:16

quote:
Originally posted by sanjnep

Is there any function in SQL to compare largest value among three columns?

dt1 dt2 dt3 maxval (I need this output)
.................................
1 3 4 4
4 4 5 5
6 6 3 6

The problem becomes complicated (more cases, more comparisons) if you have > 3 columns to compare for to get the max value, right?
So, what is the best case to solve this problem if the number of columns of the table is large?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-05-28 : 14:43:48

Best way to solve it would be to normalize your database so that this situation doesn't exist.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Database maintenance routines:
http://weblogs.sqlteam.com/tarad/archive/2004/07/02/1705.aspx

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources