Seqence 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 2012 Forums

Transact-SQL (2012)

Seqence number

Author

Topic

geossl
Yak Posting Veteran

85 Posts

Posted - 2014-08-08 : 11:42:14

Dear All,

I want to get a sequence number /group number, which increments 1 for every 3 rows.

For Example:

Seq Name

1 a

1 b

1 c

2 D

2 E

2 F

3 G

3 H

3 I

4 abc

4 aaa

4 bbb

Is there a function to do this?

MichaelJSQL
Constraint Violating Yak Guru

252 Posts

Posted - 2014-08-08 : 11:49:47

CREATE TABLE #Letters
(ID int IDENTITY(1,1), Letter varchar(4) )

INSERT INTO #Letters VALUES('A'),('B'),('C'),('D'),('E'),('F'),('G'),('H'),('I'),('ABC'),('AAA'),('bbb')

-- end set up
;With MyCTE
AS
(
SELECT * ,
ROW_NUMBER () OVER (ORDER BY ID) R
FROM #letters
)

SELECT Letter, ((R-1)/3)+1 FROM MyCTE

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources