I have two numbers as input from the user, like for example 1000
and 1050
.
How do I generate the numbers between these two numbers, using a sql query, in seperate rows? I want this:
1000
1001
1002
1003
.
.
1050
I have two numbers as input from the user, like for example 1000
and 1050
.
How do I generate the numbers between these two numbers, using a sql query, in seperate rows? I want this:
1000
1001
1002
1003
.
.
1050
This uses procedural code and a table-valued function. Slow, but easy and predictable.
Usage:
It's a table, so you can use it in joins with other data. I most frequently use this function as the left side of a join against a GROUP BY hour, day etc to ensure a contiguous sequence of time values.
Performance is uninspiring (16 seconds for a million rows) but good enough for many purposes.
Here are couple quite optimal and compatible solutions:
I know I'm 4 years too late, but I stumbled upon yet another alternative answer to this problem. The issue for speed isn't just pre-filtering, but also preventing sorting. It's possible to force the join-order to execute in a manner that the Cartesian product actually counts up as a result of the join. Using slartidan's answer as a jump-off point:
If we know the range we want, we can specify it via @Upper and @Lower. By combining the join hint REMOTE along with TOP, we can calculate only the subset of values we want with nothing wasted.
The join hint REMOTE forces the optimizer to compare on the right side of the join first. By specifying each join as REMOTE from most to least significant value, the join itself will count upwards by one correctly. No need to filter with a WHERE, or sort with an ORDER BY.
If you want to increase the range, you can continue to add additional joins with progressively higher orders of magnitude, so long as they're ordered from most to least significant in the FROM clause.
Note that this is a query specific to SQL Server 2008 or higher.
The best option I have used is as follows:
I have generated millions of records using this and it works perfect.
I recently wrote this inline table valued function to solve this very problem. It's not limited in range other than memory and storage. It accesses no tables so there's no need for disk reads or writes generally. It adds joins values exponentially on each iteration so it's very fast even for very large ranges. It creates ten million records in five seconds on my server. It also works with negative values.
It's handy for date and time ranges as well:
You could use a cross apply join on it to split records based on values in the table. So for example to create a record for every minute on a time range in a table you could do something like:
Oracle 12c; Quick but limited:
Note: limited to row count of all_objects view;