Is possible to select a numerical series or date series in SQL? Like create a table with N rows like 1 to 10:
1
2
3
...
10
or
2010-01-01
2010-02-01
...
2010-12-01
Asked
Active
Viewed 164 times
1
Daniel Santos
- 14,328
- 21
- 91
- 174
-
yes, it is possible, for example with temp table and for loop, like here http://stackoverflow.com/questions/5125096/for-loop-example-in-mysql – Iłya Bursov Feb 29 '16 at 22:05
-
not create a new table, but just select as a helper for another queries – Daniel Santos Feb 29 '16 at 22:11
-
It's possible, but not usually necessary. – Strawberry Feb 29 '16 at 23:10
3 Answers
1
If you install common_schema, you can use the numbers table to easily create queries to output those types of ranges.
For example, these 2 queries will produce the output from your examples:
select n
from common_schema.numbers
where n between 1 and 10
order by n
select ('2010-01-01' + interval n month)
from common_schema.numbers
where n between 0 and 11
order by n
Ike Walker
- 64,401
- 14
- 110
- 109
1
An SQL solution:
SELECT *
FROM (
SELECT 1 as id
UNION SELECT 2
UNION SELECT 3
UNION SELECT 4
UNION SELECT 5
)
cosmos
- 2,263
- 1
- 17
- 30
-1
Yep! Both MySQL and Microsoft SQL Server (and others) have a BETWEEN operator. I don't remember off the top of my head if it's inclusive or exclusive, but here's a starting point!
Joe
- 4,710
- 1
- 11
- 7
-
"between" operator is like a ">" and "<" operator, it does not generate a table with a range. – Daniel Santos Feb 29 '16 at 22:13