ansaurus

Question

Answer 1

A:

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?

shinod 2010-07-03 07:56:38

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 2010-07-03 08:19:25

Answer 2

+1 A:

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.

SPE109 2010-07-03 08:07:35

so there isn't a standard sql syntax to accomplish such a thing?

ali 2010-07-03 08:14:45

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 2010-07-03 08:44:03

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!

MaasSql 2010-07-03 18:02:23

Answer 3

A:

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.

Mike 2010-07-03 08:23:34

sorry, im not familiar with variables.please give me a simple view that contains values 0 to 10

ali 2010-07-03 08:27:04

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 2010-07-03 09:05:00

Answer 4

A:

egrunin 2010-07-03 18:47:30

Answer 5

+2 A:

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.

Thomas 2010-07-03 19:19:51

ansaurus

tags:

views:

answers:

SQL, counting from 0 to 10

related questions