max value row wise query - 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

General SQL Server Forums

New to SQL Server Programming

max value row wise query

Author

Topic

nextaxtion
Yak Posting Veteran

54 Posts

Posted - 2015-01-25 : 12:15:13

hi team,

I have a table having 3 coulmns.

amnt1 amnt2 amnt3
100 200 230
200 170 120
290 188 299
800 170 120

i need to get max amount row wise like

amnt
230
200
299
800

prithvi nath pandey

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-01-25 : 12:40:33

[code]
declare @t table (amnt1 int, amnt2 int, amnt3 int)
insert into @t values
(100,200,230),
(200,170,120),
(290,188,299),
(800,170,120)

select (select max(amt) from (values (amnt2), (amnt2), (amnt3)) v(amt)) maxamt
from @t
[/code]

waterduck
Aged Yak Warrior

982 Posts

Posted - 2015-01-26 : 03:30:27

declare @a table(amnt1 int, amnt2 int, amnt3 int)
insert into @a select
100, 200, 230 union all select
200, 170, 120 union all select
290, 188, 299 union all select
800, 170, 120

select *, case when
case when amnt1 > amnt2 then amnt3 end
< amnt1 then
amnt1
else
case when amnt2 > amnt3 then amnt2 else amnt3 end
end
from @a

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2015-02-02 : 01:27:38

Also refer point 3 http://beyondrelational.com/modules/2/blogs/70/posts/10905/interesting-enhancements-to-the-values-clause-in-sql-server-2008.aspx

Madhivanan

Failing to plan is Planning to fail

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-02-02 : 07:07:59

@madh that blog post contains the same solution I posted above

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources