SQL, counting from 0 to 10

Question

it may sound stupid, but i want to know how (if possible) to count from 0 to 10 by a sql command. i mean i want a sql query which produces one column consisting of numbers from 0 to 10, like this:

0
1
2
3
4
5
6
7
8
9
10

i'm currently using MySQL 5.0

Answer 1

If you simply want the exact values 0 through 10, then the following will work in all DBMS:

Select Num
From    (
        Select 0 As Num
        Union All Select 1
        Union All Select 2
        Union All Select 3
        Union All Select 4
        Union All Select 5
        Union All Select 6
        Union All Select 7
        Union All Select 8          
        Union All Select 9
        Union All Select 10
        ) As Numbers

That said, the simplest way to solve problems where you need a sequential list of integers is to create a permanent Numbers or Tally table that contains this list. In this way, it will not matter how it was populated as it need only be populated once.

Answer 2

select number  as n
from master..spt_values 
where type='p' 
and number between 0 and 10

Sorry I just noticed you were in MYSQL, this is MSSQL Only.