Is possible create numbered sequences

Question

Post reply

Is possible create numbered sequences

azdeji

SSCrazy

Points: 2535
More actions
August 6, 2013 at 10:18 am

#276725

ROW_ID
1
2
2
3
3
3
4
4
4
4
5
5
5
5
5
6
6
6
6
6
6
7
7
7
7
7
7
7
8
8
8
8
8
8
8
8
9
9
9
9
9
9
9
9
9
10
10
10
10
10
10
10
10
10
10
11
12
12
13
13
13
Up to 10000
Is possible create numbered sequences like the ROW_ID above, where the row numbers repeat themselves up to 10000 into an empty column?
In SQL server
Thanks

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply

matzer SSCrazy Eights Points: 9727 More actions · Answer 1

Something like this?

WITH Digits(D) AS -- Digits 0 to 9

(

SELECT

D

FROM

(

VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9)

) V(D)

),

Numbers(N) AS -- Numbers 1 to 10000

(

SELECT

ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) N

FROM

Digits D1, Digits D2, Digits D3, Digits D4

)

SELECT

NS.N

FROM

Numbers NS

CROSS APPLY -- Create copies based on the last digit of number N

(

SELECT

D.D

FROM

Digits D

WHERE

D.D <= (NS.N + 9) % 10

) CA(D)

ChrisM@home SSC-Insane Points: 24260 More actions · Answer 2

WITH Tens AS (SELECT * FROM (VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) d (n)),

iTally AS (SELECT n = ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM Tens a CROSS JOIN Tens b CROSS JOIN Tens c)

SELECT o.n

FROM iTally o

CROSS APPLY (SELECT TOP (o.n) n FROM iTally ) x

[font="Arial"]^{Low-hanging fruit picker and defender of the moggies}[/font]

For better assistance in answering your questions, please read this[/url].

Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]

Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]

ChrisM@home SSC-Insane Points: 24260 More actions · Answer 3

Peter Brinkhaus (8/6/2013)
Something like this?
...

Check out the last 20 rows of your result set, Peter.

[font="Arial"]^{Low-hanging fruit picker and defender of the moggies}[/font]

For better assistance in answering your questions, please read this[/url].

Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]

Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]

ChrisM@home SSC-Insane Points: 24260 More actions · Answer 4

azdeji (8/6/2013)
...
Up to 10000
Is possible create numbered sequences like the ROW_ID above, where the row numbers repeat themselves up to 10000 into an empty column?
In SQL server
Thanks

Yes it is. My query runs up to 1000 and generates over half a million rows (in 2.4 seconds). If you really need more rows than this, add an extra CROSS JOIN. Extending to include the number 5000 yields 12,502,500 rows...

[font="Arial"]^{Low-hanging fruit picker and defender of the moggies}[/font]

For better assistance in answering your questions, please read this[/url].

Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]

Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]

azdeji SSCrazy Points: 2535 More actions · Answer 5

Both SQL codes work perfectly!

Thanks Peter Brinkhaus and ChrisM@home

matzer SSCrazy Eights Points: 9727 More actions · Answer 6

ChrisM@home (8/6/2013)
Peter Brinkhaus (8/6/2013)
Something like this?
...
Check out the last 20 rows of your result set, Peter.

What's wrong with it? Based on the sample data presented, especially

...

11

12

13

...

I concluded that each number between 1 and 10000 has to be repeated based on the last digit. If the last digit D = 0, repeat it 10 times else repeat it D times. But I might be wrong.

matzer SSCrazy Eights Points: 9727 More actions · Answer 7

azdeji (8/6/2013)
Both SQL codes work perfectly!
Thanks Peter Brinkhaus and ChrisM@home

Strange, because my solution gives an entirely different result then the solution of Chris

ChrisM@home SSC-Insane Points: 24260 More actions · Answer 8

Peter Brinkhaus (8/6/2013)
azdeji (8/6/2013)
Both SQL codes work perfectly!
Thanks Peter Brinkhaus and ChrisM@home
Strange, because my solution gives an entirely different result then the solution of Chris

Oh Peter, my humblest apology. I do believe you read the spec correctly and I didn't. You are, of course, absolutely correct.

azdeji, the results are completely different. It's up to you to decide which, if either, is correct - but the evidence points towards Peter's post, which matches your requirement exactly.

[font="Arial"]^{Low-hanging fruit picker and defender of the moggies}[/font]

For better assistance in answering your questions, please read this[/url].

Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]

Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]

matzer SSCrazy Eights Points: 9727 More actions · Answer 9

ChrisM@home (8/6/2013)
Peter Brinkhaus (8/6/2013)
azdeji (8/6/2013)
Both SQL codes work perfectly!
Thanks Peter Brinkhaus and ChrisM@home
Strange, because my solution gives an entirely different result then the solution of Chris
Oh Peter, my humblest apology. I do believe you read the spec correctly and I didn't. You are, of course, absolutely correct.

Chris, no need to apology. We just interpreted the sequence of numbers differently. And the OP just made it more confusion by telling us that the two totally different solutions work well for him.

Jeff Moden SSC Guru Points: 1003922 More actions · Answer 10

azdeji (8/6/2013)
Both SQL codes work perfectly!
Thanks Peter Brinkhaus and ChrisM@home

Do you understand how they work?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

azdeji SSCrazy Points: 2535 More actions · Answer 11

Thanks both for the sql code.

Peter SQL code is what I need from my request and the other SQL code can be used for my test data generating scrip.

Is also possible to generate sql test data like the one below for 10000 rows?

Where all but two columns will have the same values repeating and ShortType will alternate between “Long” and “Short”. Short_Id will number will be repeat by 2

ShortTypeOutstandingShort_Id

Long 2333.12 1

Short 2333.12 1

Long 2333.12 2

Short 2333.12 2

Long 2333.12 3

Short 2333.12 3

Long 2333.12 4

Short 2333.12 4

Long 2333.12 5

Short 2333.12 5

SQL Code so far

Select

Long' AS ShortType ---- Long,Short every other row

,'2333.12' AS Outstanding

,' ' AS Short_ID ?

Thanks for any help?

Jeff Moden SSC Guru Points: 1003922 More actions · Answer 12

Gosh, I'm sorry. I see that this post is 6 weeks old and no one picked up on it. The following will do it for you. Change the "100000" to the largest number that you want for the Short_ID and run it. It runs nasty fast, too! With 100000, it takes less than 2 seconds to return to the screen. It'll take even less time if you insert it into a table.

SELECT TOP (100000*2)

caType.ShortType

,caType.Outstanding

,Short_ID = (ROW_NUMBER() OVER (ORDER BY (SELECT 1))-1)/2+1

FROM master.sys.all_columns ac1

CROSS JOIN master.sys.all_columns ac2

CROSS JOIN (

SELECT 'Long',2333.12 UNION ALL

SELECT 'Short',2333.12

)caType(ShortType,Outstanding)

;

If you have any questions about how it works, please don't hesitate to post back and ask.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)