seq no reseting prob - 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

seq no reseting prob

Author

Topic

qutesanju
Posting Yak Master

193 Posts

Posted - 2009-06-04 : 12:01:18

create table #temp
(s_no int ,
f_name varchar(100),
dept varchar(100)
)
insert into #temp values ( 100 , 'Rob', 'SQL Stuff')
insert into #temp values ( 100 , 'Dick', 'Government')
insert into #temp values ( 200 , 'Neil', 'Rhythm Section')
insert into #temp values ( 200 , 'Jason', 'Rhythm Section')
insert into #temp values ( 300 , 'Jodi Ann', 'Fantasy')
insert into #temp values ( 300 , 'Ashley', 'Fantasy')
insert into #temp values ( 400 , 'Jason', 'Finance')

select * from #temp

how can I add a sequence column which is resetting at s_no changing..
like output must be
1 100 Rob SQL Stuff
2 100 Dick Government
1 200 Neil Rhythm Section
2 200 Jason Rhythm Section
1 300 Jodi Ann Fantasy
2 300 Ashley Fantasy
1 400 Jason Finance

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2009-06-04 : 12:09:36

I'd suggest that it's probably a bad idea to put this into a table. If you care to explain what you are doing we might be able to help come up with a better solution. But, If you are using 2005 or later, and you really want to do this, you can take advantage of the ROW_NUMBER() function.

qutesanju
Posting Yak Master

193 Posts

Posted - 2009-06-04 : 12:28:11

ca nu pls have a sample query for this

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-06-04 : 12:37:48

[code]SELECT ROW_NUMBER() OVER (PARTITION BY s_no ORDER BY f_name DESC) AS SEq,* FROM #temp[/code]

qutesanju
Posting Yak Master

193 Posts

Posted - 2009-06-05 : 03:26:29

excellent visakh16.

qutesanju
Posting Yak Master

193 Posts

Posted - 2009-06-05 : 03:33:48

SELECT (SELECT ROW_NUMBER() OVER (PARTITION BY s_no ORDER BY f_name DESC) AS SEQ,* FROM #temp)
INTO #Result
FROM #temp
but this statement fails...

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-05 : 04:00:49

SELECT ROW_NUMBER() OVER (PARTITION BY s_no ORDER BY f_name DESC) AS SEQ,
*
INTO #Result
FROM #temp

E 12°55'05.63"
N 56°04'39.26"

qutesanju
Posting Yak Master

193 Posts

Posted - 2009-06-05 : 04:14:46

thanks.......actually i tried to delete duplicate numbers......by giving seq no..
by this way..
Delete from #result
Where SEQ not in (select min (SEQ) from #result where s_no = s_no group by s_no)

but how can i attach sequence number .......by in SQL server 2000 without using row_number()function .....SELECT ROW_NUMBER() OVER (PARTITION BY s_no ORDER BY f_name DESC) AS SEQ,...coz row_number() is not applicable ......for sql server 2000

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-06-05 : 13:18:40

do you have any unique valued column in your table currently?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources