add serial number

[Itzik Ben-Gan]

Jane,

If you have a unique identifier within each group, e.g., col2 in the following schema:

CREATE TABLE T1
(
col1 CHAR(1) NOT NULL,
col2 INT NOT NULL,
PRIMARY KEY(col1, col2)
)

INSERT INTO T1 VALUES('A', 10)
INSERT INTO T1 VALUES('A', 20)
INSERT INTO T1 VALUES('A', 30)
INSERT INTO T1 VALUES('B', 5)
INSERT INTO T1 VALUES('B', 40)
INSERT INTO T1 VALUES('C', 7)
INSERT INTO T1 VALUES('C', 13)
INSERT INTO T1 VALUES('C', 99)

You can use the following query:

SELECT *,
(SELECT COUNT(*)
FROM T1 AS B
WHERE B.col1 = A.col1
AND B.col2 <= A.col2) AS pos
FROM T1 AS A

If you don't, you can always add one (IDENTITY column):

ALTER TABLE T1 ADD keycol INT NOT NULL IDENTITY
CREATE INDEX idx1 ON T1(col1, keycol)

SELECT *,
(SELECT COUNT(*)
FROM T1 AS B
WHERE B.col1 = A.col1
AND B.keycol <= A.keycol) AS pos
FROM T1 AS A

If you don't and you'd rather not add a new column, create an auxiliary table of numbers:

CREATE TABLE Nums(n INT NOT NULL PRIMARY KEY)
DECLARE @i AS INT
SET @i = 1
BEGIN TRAN
WHILE @i <= 1000 -- make sure enough for max num of rows in group
BEGIN
INSERT INTO Nums VALUES(@i)
SET @i = @i + 1
END
COMMIT

And use the following query:

SELECT col1, n
FROM (SELECT col1, COUNT(*) AS cnt
FROM T1
GROUP BY col1) AS T
JOIN Nums
ON n <= cnt

--
BG, SQL Server MVP
Solid Quality Learning
[url]www.solidqualitylearning.com[/url]


"Jane" <janek@123.com> wrote in message news:025301c34636$ecd0c8b0$a101280a@phx.gbl...

> This is the 3rd time I tried to post the same ??? on this
> board. I posted the first one for more than 1.5 hrs. ago,
> it has not shown up on the board.
>
> Question: How can I add number(in sequential order) based
> on the values on another column. Whenever the value
> changes again, the number need to restart as '1' again. It
> will be something look like this:
> COLUMN1: A A A B B C C C
> NEW COL: 1 2 3 1 2 1 2 3
>

[Bush]

Hi,
Elaborate ur question!!!

Thanks,
Bush.

>-----Original Message-----
>This is the 3rd time I tried to post the same ??? on this
>board. I posted the first one for more than 1.5 hrs. ago,
>it has not shown up on the board.
>
>Question: How can I add number(in sequential order) based
>on the values on another column. Whenever the value
>changes again, the number need to restart as '1' again.

It

>will be something look like this:
>COLUMN1: A A A B B C C C
>NEW COL: 1 2 3 1 2 1 2 3
>
>.
>

[Jane]

Thank you very much!

Since I don't have a primary key. I use your second method
and IT WORKS!!!

I wish MS can make this simple process as less 'painful'
as it can be.

>-----Original Message-----
>Do you have a Primary Key on this table? If so:
>
>ALTER TABLE Sometable ADD newcol INTEGER NULL
>
>GO
>UPDATE Sometable
> SET newcol =
> (SELECT COUNT(*)
> FROM Sometable AS S
> WHERE column1=Sometable.column1
> AND keycol<=Sometable.keycol)
>
>
>If not, I think you will have to create a new table as

follows.

>
>Create a numbers table at least as big as the largest

group of Column1

>values:
>
>CREATE TABLE Numbers (num INTEGER PRIMARY KEY)
>
>WHILE (SELECT COALESCE(MAX(num),0) FROM Numbers) < 1000
> INSERT INTO Numbers
> SELECT COALESCE(MAX(num),0)+1
> FROM Numbers
>
>
>Then create and populate a new table like this:
>
>CREATE TABLE Newtable (column1 CHAR(1) NOT NULL, newcol

INTEGER NOT NULL,

>PRIMARY KEY (column1,newcol))
>
>INSERT INTO newtable
> SELECT C.column1, N.num
> FROM
> (SELECT column1, COUNT(*) AS cnt
> FROM Sometable
> GROUP BY column1) AS C
> JOIN Numbers AS N
> ON N.num <= C.cnt
>
>
>--
>David Portas
>------------
>Please reply only to the newsgroup
>--
>
>
>
>.
>

[David Portas]

> I wish MS can make this simple process as less 'painful'

> as it can be.

The least painful solution is to ensure every table has a primary key,
without fail. Time spent on good design will always pay off in the end.

--
David Portas
------------
Please reply only to the newsgroup
--