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]
Go to Top of Page

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
Go to Top of Page

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
Go to Top of Page

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
Go to Top of Page
   

- Advertisement -