how to put sequence numbering in a new column? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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)

how to put sequence numbering in a new column?

Author

Topic

anabelle
Starting Member

19 Posts

Posted - 2009-04-02 : 05:31:31

Hello EveryOne,i am back again with this kind of delimma,my boss wanted a result tommorow, and i am scared if how wud i do it...okay the scenario is this we havve records into the database and he wanted to extract records and add colums to it and sequencially number them.ex:
FruitsTable, populated with Apple,Mango,Orange,Banana
And He wants to group that like this way, and he will add column to that, letsay the colmname is SequenceNo.,if the Apple has 5 records, he wanted to create a sequence number into the new column
FruitsName SeqNo.
Apple 1

2

3

Mango 1

2

that's samting result i wanted to have, please someone cud help me. do i have to compute the new column?how wud i assign automatic sequence of number???please someone cud help me???

ayamas
Aged Yak Warrior

552 Posts

Posted - 2009-04-02 : 05:48:31

Is this what you want?

declare @tbl as table(fruitsname varchar(40))
insert into @tbl
select 'apple' union all
select 'apple' union all
select 'banana' union all
select 'papaya' union all
select 'papaya'

select fruitsname,ROW_NUMBER() over(partition by fruitsname order by fruitsname)
as sequenceid from @tbl

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-04-02 : 06:07:23

if u r using sql 2000
try this one


declare @tbl as table(fruitsname varchar(40))
insert into @tbl
select 'apple' union all
select 'apple' union all
select 'banana' union all
select 'papaya' union all
select 'papaya'

select identity(int,1,1) as rid, * into #temp from @tbl

select *,(select count(*) from #temp where rid <= t.rid and fruitsname = t.fruitsname)
from #temp t

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-04-02 : 06:42:45

quote:

Originally posted by bklr

if u r using sql 2000
try this one


declare @tbl as table(fruitsname varchar(40))
insert into @tbl
select 'apple' union all
select 'apple' union all
select 'banana' union all
select 'papaya' union all
select 'papaya'

select identity(int,1,1) as rid, * into #temp from @tbl

select *,(select count(*) from #temp where rid <= t.rid and fruitsname = t.fruitsname)
from #temp t

The second method would be time consuming if there are many rows in the table

Madhivanan

Failing to plan is Planning to fail

anabelle
Starting Member

19 Posts

Posted - 2009-04-15 : 03:25:46

madhivanan it didnt work actually i need not to insert anything on my table because those table already exist, so what specifically it needs to works is to thru an sql script or sql query that would simply select those example table mentioned and create a sub query that would count the table i need to be group and have a sequential numbering....i hope u were able to get me...

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-04-15 : 08:48:04

quote:
Originally posted by anabelle

madhivanan it didnt work actually i need not to insert anything on my table because those table already exist, so what specifically it needs to works is to thru an sql script or sql query that would simply select those example table mentioned and create a sub query that would count the table i need to be group and have a sequential numbering....i hope u were able to get me...

What happens when you run this query?

select fruitsname,ROW_NUMBER() over(partition by fruitsname order by fruitsname)
as sequenceid from your_table

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources