generate sequence number basing on each id

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

generate sequence number basing on each id

Author

Topic

devisetti
Starting Member

30 Posts

Posted - 2008-03-28 : 01:36:49

Hi all
anbody can help me writing sql code for this. All i need is to generate sequence basing on id_no
Ex: if ID=ABC(twice) in seq_col as abc --1
abc ---2
Tables which I have
Uniques_No ID_NO SEQ
---------------------------------------------
1 ABC
2 ABC
3 ABC
4 BBC
5 BBC

Expected results as below :
------------------

Uniques_No ID_NO SEQ
---------------------------------------------
1 ABC 1
2 ABC 2
3 ABC 3
4 BBC 1
5 BBC 2

Thanks in advance

pravin14u
Posting Yak Master

246 Posts

Posted - 2008-03-28 : 01:50:15

select ID_NO,row_number() over(partition by ID_NO order by ID_NO) from TABLE_NAME

This will work only in SQL Server 2005!

Which one are you using?

sunil
Constraint Violating Yak Guru

282 Posts

Posted - 2008-03-28 : 01:52:00

if you are using sql server 2005, use RANK or ROW_NUMBER function.
TRY
SELECT Uniques_No, ID_NO, ROW_NUMBER ( ) OVER ( PARTITION BY ID_NO ORDER BY Uniques ) SEQ FROM yourtable

devisetti
Starting Member

30 Posts

Posted - 2008-03-28 : 02:02:52

I am using sql server 2005

devisetti
Starting Member

30 Posts

Posted - 2008-03-28 : 02:24:30

Many thanks guys its work fine

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources