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

See [Number Sequence in MySQL](http://stackoverflow.com/questions/2495487/number-sequence-in-mysql) — Matthew Flaschen, Jul 03 '10 at 08:03
@egrunin, that's the same thing the prior question is doing. The only real difference is that uses 0-9 as an example. — Matthew Flaschen, Jul 03 '10 at 23:40
@Matthew Flaschen: ah, I hadn't seen the answer from @geertjanvdk, which does address the problem. I'll give it a +1 so it rises to the top :) — egrunin, Jul 04 '10 at 04:54

score 3 · Answer 1 · answered Jul 03 '10 at 19:19

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.

score 2 · Answer 2 · answered Jul 03 '10 at 08:07

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.

answered Jul 03 '10 at 08:07

SPE109

2,911
1
19
16

so there isn't a standard sql syntax to accomplish such a thing? – ali Jul 03 '10 at 08:14
This just reads values from the SQL system table, I'm not familiar with MYSQL so the only thing I could suggest is you create a numbers table - I think many people do and use this. – SPE109 Jul 03 '10 at 08:44
The 'standard' way in MS SQL Server apparently applies to MySQL as well, judging from the question Matthew Flaschen referred you to, Number Sequence in MySQL. Reading both questions, I'd say SPE109's answer is the best. Create a 'utility' table. In ms sql server, master.dbo.spt_values only has 0-2048 in it, and is 'yuck' a master table. In my code, I create my own table that's always around, which is always populated and not messed with. I refer to it like this: select n from numbers where n >=1 and n <= 10. Same approach, guaranteed to work in MySQL. Good luck! – Jeff Maass Jul 03 '10 at 18:02

score 1 · Answer 3 · answered Jul 03 '10 at 08:23

1

You can use a variable for that (although I'm not 100% clear on your requirements):

SELECT id, (@i := @i + 1) AS counter FROM <table>,
(SELECT @i := 0) AS vars WHERE @i <= 10;

You can include whatever other columns you like - I've just used id as an example. If the table from which you are selecting has at least 11 rows, this will work. The parenthesised SELECT is executed first, and initialises the variable @i to zero.

answered Jul 03 '10 at 08:23

Mike

21,301
2
42
65

sorry, im not familiar with variables. please give me a simple view that contains values 0 to 10 – ali Jul 03 '10 at 08:27
If the idea of having a view to which you can join an existing table is purely to obtain a list of sequential numbers, then I think you are making things more difficult for yourself, whilst at the same time, involving a join where one is not needed. Can you edit your question to make your exact requirements clear? – Mike Jul 03 '10 at 09:05
Thats an elegant way to generate range – Arkemlar Apr 05 '19 at 12:29

score 0 · Answer 4 · answered Jul 03 '10 at 07:56

0

Using pl sql we can do as follows

for i in 1..1000 loop

insert into a values(i,i*2);

end loop;

Is it helpful to solve your doubt?

answered Jul 03 '10 at 07:56

shin

1,675
4
22
46

sorry, i forgot. i want to have these values in a simple view to outer join it with a table that doesn't have all these values as ids, where i need all of them. off course i can create a table and insert these values one by one, but i was curious that there may not need to create another table for such a simple thing. – ali Jul 03 '10 at 08:19

score 0 · Answer 5 · edited May 23 '17 at 10:29

0

Just for reference, this answer shows that it can be done in Oracle, PostGres, and MS Sql—but not MySQL, alas.

edited May 23 '17 at 10:29

Community

1
1

answered Jul 03 '10 at 18:47

egrunin

24,650
8
50
93

SQL, counting from 0 to 10

5 Answers5