How often have you needed to start a sequence of numbers at other than 1 or 0?

Question

How often have you needed to start a sequence of numbers at other than 1 or 0?

March 2, 2021 at 7:17 am

This was removed by the editor as SPAM

Viewing 9 posts - 16 through 23 (of 23 total)

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

Jeff Moden SSC Guru Points: 1003894 More actions · Answer 1

Steve Collins wrote:

I use 2 monster .sql scripts to answer SSC/SO questions. One currently has 13,787 lines and contains 32 occurrences of 'fnTally' of which 24 specify the sequence begin with 1. Two has 6,859 lines and contains 14 occurrences of fnTally of which 9 specify beginning with a 1. In total 33/46 or about 72%.

Awesome stats. Thank you good sir. And, based on your previous replies, the other 13 or so all start at "0"?

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

Erland Sommarskog SSC-Insane Points: 24038 More actions · Answer 2

I dug around in the system I work with daily and I found:

 join numbers n on n.n between p.startYM/100 and p.endYM/100

The values where would be something like 2019 to 2021. (That is, years.).

In a unit test I found:

 INSERT OrganizationUnit(ouid)
SELECT n
FROM Numbers
WHERE n BETWEEN 1 AND 1000
UNION ALL
SELECT n
FROM Numbers
WHERE n BETWEEN 10001 AND 11000

I don't recall exactly how I use the second set, but presumably I add 10000 to the ouids somewhere.

In any case, the choice of 1 to 1000 is entirely arbitrary. I could just as well have taken 1211 to 2210.

[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]

Jeff Moden SSC Guru Points: 1003894 More actions · Answer 3

Thanks, Erland. Interesting use case for the year/100 stuff.

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

Steve Collins SSCrazy Points: 2077 More actions · Answer 4

Jeff Moden wrote:

Awesome stats. Thank you good sir. And, based on your previous replies, the other 13 or so all start at "0"?

Yes, the other 13 start with 0, meaning 'dbo.fnTally(0, ...)'. That's how the function is being called but many times a constant is added to the sequence either in the SELECT list or as a CROSS JOIN'ed calculated value.

Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

Erland Sommarskog SSC-Insane Points: 24038 More actions · Answer 5

Here is another example based on what I found to day:

SELECT char(n) FROM numbers WHERE n BETWEEN acsii('A') AND ascii('Z')

In the actual example I found when from 0 to 31, because my colleague wanted to deal with control characters, but you get the idea.

[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]

Jeff Moden SSC Guru Points: 1003894 More actions · Answer 6

Erland Sommarskog wrote:

Here is another example based on what I found to day:
SELECT char(n) FROM numbers WHERE n BETWEEN acsii('A') AND ascii('Z')
In the actual example I found when from 0 to 31, because my colleague wanted to deal with control characters, but you get the idea.

That a good "cheater" way to do things if you don't have those key values memorized. It would also be interesting to see what they were doing in the rest of the query for that particular use of a Numbers table. It IS one of the very few examples I was thinking of where you might not want to start at "0" or "1" but could easily work around it.

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

Erland Sommarskog SSC-Insane Points: 24038 More actions · Answer 7

It would also be interesting to see what they were doing in the rest of the query for that particular use of a Numbers table.

Actually, I had to rewrite the query, because it relied on undefined behaviour. The purpose was to remove all control characters. It went

SELECT @xmlstr = replace(@xmlstr, char(n), '') FROM numbers WHERE number n BETWEEN 0 AND 31

This XML is coming from another system, and apparently the XML is sometimes garbled.

A second problem is that this will never work for char(0) since that is undefined character unless you cast to a binary collation. A third problem is that the numbers table in questions starts on 1...

I rewrote the operation using transpose. Still using numbers, but I had to compensate for the missing 0.

[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]

Jeff Moden SSC Guru Points: 1003894 More actions · Answer 8

Sounds like a road trip to the data provider with a pork chop cannon may be in order. 😀

Yeah... I know. It didn't work for me either. All I got was a "This is the way we've always done it" answer. No interest in doing it right.

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