Data Tagging 1

[delaap]

Hi Folks,

I'm looking to create a script that will do the following:
Order a Field ASC
and then create a number sequence on another field. The sequence is from 1 thru to 8 i.e.
row1 = 1
row2 = 2
row3 = 3
.
..
row8 = 8
row9 = 1
row10 = 2
row11 = 3 etc ........

Any ideas ?

 

[AWithers]

One way is to insert the data into a temparary table which has an identifier field starting with 1 (with also the key information)

then update the source table based on the numbers in the temporary table

then drop the temporary table

Possibly the long way round but will work

Andy

 

[tlbroadbent]

Do you want to update the table or just display a result set? It appears to me that you want to return a result. I agreee with Andy about using a temporary table. You can use a table variable in SQL 2000.

Create table #tmp
(col1 <data type>,col2 <data type>, ...
colN <data type>, RowNo int Identity)

Insert #tmp (col1, col2, ..., colN)
Select <column list>
From YourTable
Order By <sort columns>

Select
col1, col2, ..., colN,
RowNo=
--Use the Modulo operator to number
--the rows from 1 to 8.
Case RowNo % 8
When 0 Then 8
Else RowNo
End
From #tmp
Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains &quot;Suggestions for Getting Quick and Appropriate Answers&quot; to your questions.

 

[bperry]

I liked AWithers' suggestion, so I tried it to see how it worked. Rather than delete my results, thought I would post it here in case it's of any value to you. Thanks, AW.

[tt]
Select TitleId, Title, Price From Books

TitleId Title Price
------------------------------ -------
1 Another Rainy Day 19.99
2 The Quick Brown Fox 21.00
3 Andy, Randy, and Kate 9.95
4 Skunk Anansie 100.00
5 To Kill A Mockingbird 5.98
6 Tribal Ancestors 17.50
7 Annual Anthems 11.25

(7 row(s) affected)

-- Create Tempory Table
CREATE TABLE #TEMPTABLE (
PkId int NOT NULL Identity,
SeqNo int NOT NULL,
Title varchar(40) NOT NULL,
Price decimal (5,2) NOT NULL,
Primary Key (PkId)
)
-- Fill temporary table
-- SeqNo is initially zero
Insert INTO #TEMPTABLE
Select 0, Title, Price
From Books Order by Price ASC

-- Now update SeqNo
declare @limit int
set @limit = 3 -- this will be 8 in your case
Update #TEMPTABLE
SET SeqNo =
(CASE when Pkid % @limit = 0
Then @limit
Else PkId % @limit END)

-- Report result and finish up
Select PkId, SeqNo, Price
From #TEMPTABLE -- see result below
drop table #TEMPTABLE
----------------------------
PkId SeqNo Price
----------- ----------- -------
1 1 100.00
2 2 21.00
3 3 19.99
4 1 17.50
5 2 11.25
6 3 9.95
7 1 5.98

(7 row(s) affected)
----------------------------
[/tt]