identify rows - 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)

identify rows

Author

Topic

inbs
Aged Yak Warrior

860 Posts

Posted - 2009-03-17 : 05:57:36

i have :

i want

A this is Order Number,
if he found the first order so he put 1 and other of order of the same the put 0. like A=1.

how can i do it in SQL?

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-03-17 : 06:24:46

select a,case when rid = 1 then rid else 0 end as b
from (select *,row_number()over(partition by a order by a)as rid from tablename)s

inbs
Aged Yak Warrior

860 Posts

Posted - 2009-03-17 : 06:50:16

how can you do it in SQL 2000 and what is rid?

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-03-17 : 06:54:35

is there a primary key ? You'll have to define a criteria as to which one you want to update as 0/1. There is nothing like first or last when talking relational.

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-03-17 : 06:57:27

[code]In 2000 try this once
declare @t table (A int)
insert into @t select 1 union all select
1 union all select
1 union all select
2 union all select
2 union all select
3 union all select
3 union all select
3
select identity(int,1,1)as rid, * into #temp from @t

select a,case when cnt = 1 then cnt else 0 end as b
from (select a,(select count(a) from #temp where rid <= t.rid and a= t.a)as cnt
from #temp t)s

drop table #temp
[/code]

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-03-17 : 06:58:41

quote:
Originally posted by inbs

how can you do it in SQL 2000 and what is rid?

In previous query rid is columnname alias name for row_number value

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources