Sequence Number - 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 2000 Forums

Transact-SQL (2000)

Sequence Number

Author

Topic

u2p_inst
Yak Posting Veteran

78 Posts

Posted - 2007-09-06 : 08:25:59

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-09-06 : 09:09:31

Do you want to add it physically to the table, or just return a column in a SELECT that shows the sequence? Big difference! Always try to be clear exactly what you need.

Also - what is the PK of this table? Can there be more than one row for ech Cust_Name/Month combination? This is a also an important detail to specify.

- Jeff
http://weblogs.sqlteam.com/JeffS

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2007-09-06 : 09:14:16

This may change in lieu of your answers to jsmith

DECLARE @test TABLE (Cust_Name varchar (50)
,Mth int
)

INSERT INTO @test
SELECT 'Gilbert' , 5
UNION
SELECT
'Gilbert' , 4
UNION
SELECT
'Gilbert' , 7
UNION
SELECT
'Martin' , 3
UNION
SELECT
'Martin' , 9

select t1.Cust_Name,t1.mth,count(t2.mth)
from @test t1
inner join @test t2
on t1.cust_name = t2.cust_name
and t1.mth > = t2.mth
group by t1.Cust_Name,t1.mth
order by 1,2

Jim

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-09-06 : 10:05:12

quote:
Originally posted by u2p_inst

Dear All,

I want to add sequence number to a table below:

Cust_Name | Month
=======================
Gilbert | 5
Gilbert | 4
Gilbert | 7
Martin | 3
Martin | 9

and the result i need is:

SEQ_nbr | Cust_Name | Month
================================
1 | Gilbert | 4
2 | Gilbert | 5
3 | Gilbert | 7
1 | Martin | 3
2 | Martin | 9

Thanks before......

oh

If you want to show data in Reports, you can easily group the data by cust_name and reset recordnumber for each group

Madhivanan

Failing to plan is Planning to fail

u2p_inst
Yak Posting Veteran

78 Posts

Posted - 2007-09-06 : 22:59:23

thanks jimf & jsmith your answer is what i need, but now i'm interesting with issue madhivanan, how it's can be add to physicaly table?, is it possible?

oh

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources