2

Possible Duplicate:
How to generate a range of numbers in Mysql

Is it possible to write a select statement that will return a list of numbers? Like

col_a  col_b
1      1
2      2
3      3
4      4
5      5

I can get one row by doing SELECT 1, 1 but how do I get more? I'm thinking something like SELECT range(1,10), range(1,10) or SELECT @x, @x FROM @x in range(1,10)... I don't really know. Not sure if this is possible.

Yes, I know this is better suited for a different language, but I need to do it in MySQL.

Community
  • 1
  • 1
mpen
  • 272,448
  • 266
  • 850
  • 1,236
  • 1
    Would it be acceptable to have this data put in a temporary table? What is the context of this "select range" query - does it need to be nested in something larger? Does it need to all happen in a single statement? – jwd Jul 15 '11 at 18:49
  • @jwd: Has to be done in one query, preferably no temporary table. The query gets passed to some PHP which loops over the results and puts it into a ` – mpen Jul 15 '11 at 18:54
  • nice, the crossjoin one is genius! :D – Jacob Jul 15 '11 at 18:58

2 Answers2

2

One really stupid way:

SELECT 1,1 
UNION 
SELECT 2,2
....
SELECT 10,10
Jacob
  • 41,721
  • 6
  • 79
  • 81
  • 1
    `FROM SomeTableDOesntMatter` is not even necessary here – Mchl Jul 15 '11 at 18:56
  • I got an error when I tried it without, but it was an online tester. This one: http://www.w3schools.com/sql/trysql.asp – Jacob Jul 15 '11 at 18:57
  • 1
    I've just tried on my localhost in MySQL 5.1.36 and it works without FROM caluse – Mchl Jul 15 '11 at 19:00
0

Usually this is done by creating a TEMPORARY table (often using MEMORY engine) and selecting from it.

Added: Another way might be to create a stored procedure that will create a prepared statement similar to what cularis propses in his answer.

Mchl
  • 61,444
  • 9
  • 118
  • 120