SQL select generator

Question

Possible Duplicate:
generate an integer sequence in MySQL
generate many rows with mysql

Is there a (simple) mean to create an sql request that return integers between 1 and N ? ( like a range(n) in python. )

Something like :

select i 
FROM all_ints_values  
where i >= 1 and i < N;

where table all_ints_values should contains all ints values !!

Of course it souldn't be possible to create such a table. (and even if N is limited I don't want do this)

Context :

I've got a table with sales ( dated by year and month). I want to retrieve the sum of sales by year and mounth. You can do that with a group by close :

select year,month, sum(money) from my_table group by year,month order by year,month

But with this I get 'holes' in my list ( if there is no sales in January 2011) so it not suitable to make a graph.

With the generator I could use an OUTER JOIN some thing like:

select y.i,m.i, nvl(sum_money,0) LEFT OUTER JOIN mysummaryview ON y.year = year and m.month = month FROM (select int from 1990 to 2012) y , (select int from 1 to 12) m order by y.i,m.i

Could you provide some context of the end goal that you are trying to achieve? — Sergey Kalinichenko, Sep 18 '12 at 09:45
I guess you mean something like the Postgres [`generate_series`](http://www.postgresql.org/docs/9.1/static/functions-srf.html) function to generate a numbers table dynamically. — Martin Smith, Sep 18 '12 at 09:49
Postgres generates_series is exactly what I am looking for. ( but for mysql) — user1129519, Sep 18 '12 at 10:08
@user1129519, as the linked questions and the questions found by this search `[mysql] row generator` say, MySQL is the only major RDBMS which does not have an easy method of doing this... — Ben, Sep 18 '12 at 10:09

SQL select generator

0 Answers0