Numbering Rows

[Guest]

Hello,

Is it possible to number rows in a query up to a certain number, then begin
the numbering sequence again. For example: The original table currently looks
something like.

TitleName

Jim
Jan
John
Al
Frank
Sam
Kate
Sally
Susie
Amy
Kevin
Phil

I would like the Query to number each row up to 3 then restart the numbering
sequence to display an output similar to:

TitleName NumberSeq

Jim 1
Jan 2
John 3
Al 1
Frank 2
Sam 3
Kate 1
Sally 2
Susie 3
Amy 1
Kevin 2
Phil 3

Please let me know if you need more information.

Thank You for your help,

 

[Chris2]

Hello,

Is it possible to number rows in a query up to a certain number, then begin
the numbering sequence again. For example: The original table currently looks
something like.

I would like the Query to number each row up to 3 then restart the numbering
sequence to display an output similar to:

Please let me know if you need more information.

Thank You for your help,

tjh,

Tables:

Please forgive the dates appended to the table names.

Note: I specified a new column "TitleID" because according to the
desired results, the order of TitleName must be presevered, and
without some other column there to preserve that order, I don't
think it's going to work.

CREATE TABLE Titles_20051129_1
(TitleID AUTOINCREMENT
,TitleName TEXT(5)
,CONSTRAINT pk_Titles_20051129_1
PRIMARY KEY (TitleID)
)


Sample Data:

1, Jim
2, Jan
3, John
4, Al
5, Frank
6, Sam
7, Kate
8, Sally
9, Susie
10, Amy
11, Kevin
12, Phil


Query:

SELECT T1.TitleName
,(SELECT SWITCH(COUNT(T1.TitleID) MOD 3 = 1, 1
,COUNT(T1.TitleID) MOD 3 = 2, 2
,COUNT(T1.TitleID) MOD 3 = 0, 3)
FROM Titles_20051129_1 AS T01
WHERE T01.TitleID <= T1.TitleID)
AS NumberSeq
FROM Titles_20051129_1 AS T1


Results:

TitleName, NumberSeq

Jim, 1
Jan, 2
John, 3
Al, 1
Frank, 2
Sam, 3
Kate, 1
Sally, 2
Susie, 3
Amy, 1
Kevin, 2
Phil, 3

That appears to be a copy of the desired results.



If you don't have, or can't add, a column equivalent to TitleID,
then you'll have to order TitleName.


Query 2:

SELECT T1.TitleName
,(SELECT SWITCH(COUNT(T1.TitleName) MOD 3 = 1, 1
,COUNT(T1.TitleName) MOD 3 = 2, 2
,COUNT(T1.TitleName) MOD 3 = 0, 3)
FROM Titles_20051129_1 AS T01
WHERE T01.TitleName <= T1.TitleName)
AS NumberSeq
FROM (SELECT TOP 100 PERCENT
T02.TitleName
FROM Titles_20051129_1 AS T02
ORDER BY T02.TitleName) AS T1

Results 2:

TitleName, NumberSeq

Al, 1
Amy, 2
Frank, 3
Jan, 1
Jim, 2
John, 3
Kate, 1
Kevin, 2
Phil, 3
Sally, 1
Sam, 2
Susie, 3

Of course, this doesn't quite match the original desired results.


Sincerely,

Chris O.

 

[John Nurick]

Can be done. This example assumes that ID is the primary key and
demonstrates

sequential numbering of records 0123456789 (RecNum)
repeated numbering 111122223333 (SlowStep)
cyclic numbering 123412341234 (Cyclic)

SELECT A.ID,

(SELECT COUNT(ID) FROM [TheTable] AS B WHERE B.ID < A.ID) AS RecNum,

Int(((SELECT COUNT(ID) FROM [TheTable] AS C WHERE C.ID < A.ID) Mod
52)/4)+1 AS SlowStep,

Int((SELECT COUNT(ID) FROM [TheTable] AS C WHERE C.ID < A.ID) Mod 4)+1
AS Cyclic

FROM [TheTable] AS A
ORDER BY A.ID;