Re: Numbering Rows


"tjh" <tjh@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:429BCB40-60A1-4920-8DF6-92D3B585A939@xxxxxxxxxxxxxxxx
> 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.
>

<snip>

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

<snip>

>
> 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.


.