Solved

# Create List/sequence from nothing e.g. 1,2,3,4,5,.....,99,100

Posted on 2006-04-19
220 Views
How can i create a sequence of numbers or dates from nothing! i.e. i want a list of numbers 1 to 100 without referencing a table.

0
Question by:leatham

LVL 15

Expert Comment

If you just need them to be a part of WHERE clause (because that's the only case where I see NOT wanting a table to make sense) you can do

select * from mytable where myfield in (1,2,3,4,5,6,7,8,9,10 ..and so on... 99,100);
0

Author Comment

I have some very heafty queries that i need to run based on a list of dates. at the moment i use a table that has a list of dates and i simply join the dates table to a subquery of the main query to limit the amount of data that is being retrieved.

The problem is that anyone running this query is limited to the dates in the table. I want one user to be able to run the query based on a different set of dates from the an other user at the same time.

If i can know how to run a series of numbers then i can generate the dates. what is the 'in' part of your query do?

Thanks
0

LVL 15

Expert Comment

(myfield in (1,2,3)) will return a boolean true if myfield=2 and boolean false if myfield=4. You can do the same with dates instead of integers.

Now, your list of dates - what is it really, list of working days, all tuesdays, something else? Maybe you can work out a common rule to describe the dates you are interested in instead of using a "table function".
0

Author Comment

I simply need a list if consecutive dates. I know how to filter a list, i was just wondering if this was possible without referencing a table.
0

LVL 15

Expert Comment

if they are consecutive, why can't you use

(d between startDate and endDate)

?
0

LVL 22

Expert Comment

select generate_series(1,20,2), to_date('29-JAN-2002','DD-MON-YYYY') + generate_series(1,147,7);
0

LVL 22

Expert Comment

select generate_series(1,100);
0

LVL 22

Accepted Solution

I need to read the docs before I give you bad information...

select * from generate_series(1,100);

select current_date + s.a as dates from generate_series(0,14,7) as s(a);

http://www.postgresql.org/docs/8.1/static/functions-srf.html
0

## Featured Post

Best database to use for Maps is PostgreSQL. This is an open source database. Comes as a package with most Linux OS. For more info visit the following site: http://www.postgresql.org/ (http://www.postgresql.org/) This requires some add-o…
Many developers have database experience, but are new to PostgreSQL. It has some truly inspiring capabilities. I have several years' experience with Microsoft's SQL Server. When I began working with MySQL, I wanted a quick-reference to MySQL (htt…
Steps to create a PostgreSQL RDS instance in the Amazon cloud. We will cover some of the default settings and show how to connect to the instance once it is up and running.
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.