Order - 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

SQL Server 2005 Forums

Transact-SQL (2005)

Order

Author

Topic

rajdaksha
Aged Yak Warrior

595 Posts

Posted - 2009-07-29 : 10:44:23

Hi

I have table like this...


A       B       C        D 
75	5	70	 0
75	92	17	 1
75	508	433	 1
75	2116	2041	 1
75	8680	8605	 1

I have to check the condition is A<B its true D should be 1(when the condition meet first row is update to 1 else all rows are update 0)

I want like this...


A       B       C        D 
75	5	70	 0
75	92	17	 1
75	508	433	 0
75	2116	2041	 0
75	8680	8605	 0

Help out on this...

-------------------------
R..

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-07-29 : 10:52:28

Here is one way

declare @t table (A int, B int, C int, D int)
insert @t

select 75, 5, 70, 0 union all
select 75, 92, 17, 1 union all
select 75, 508, 433, 1 union all
select 75, 2116, 2041, 1 union all
select 75, 8680, 8605, 1

update @t set D = 0

update T
set T.D = 1
from @t T inner join
(select row_number() over(partition by A order by B ) as seq,* from @t
where A<B
) T1
on T.A = T1.A and T.B = T1.B and T.C = T1.C
where T1.seq = 1

select * from @t

Result
-------
A B C D
----------- ----------- ----------- -----------
75 5 70 0
75 92 17 1
75 508 433 0
75 2116 2041 0
75 8680 8605 0

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-07-29 : 11:06:11

One of the methods

declare @t table(A int, B int, C int, D int)
insert into @t
select 75, 5, 70, 0 union all
select 75, 92, 17, 1 union all
select 75, 508, 433, 1 union all
select 75, 2116, 2041, 1 union all
select 75, 8680, 8605, 1

select * from @t
update @t
set d=case when a<b then 1 else 0 end
from @t

update t
set d=case when sno=1 and d=1 then 1 else 0 end

from
(
select *,row_number() over (partition by d order by B) as sno from @t
) as t

select * from @t

Madhivanan

Failing to plan is Planning to fail

rajdaksha
Aged Yak Warrior

595 Posts

Posted - 2009-07-29 : 11:09:59

Hi vijayisonly

Thanks a lot..friend

-------------------------
R..

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-07-29 : 11:11:45

welcome

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources