using SELECT to return result set with range from min to max

[shadedecho]

(This sounds very simple, and yes there would be other programatic ways to do it, but I'm searching for a SQL only answer for some specific reasons. Also, I really feel like it needs to be in one (maybe two) SQL statements, not one for each.)

What I would like is to run a generic SELECT statement (not on a specific table) that returned a result set which had one row for each value in the range from a specified minimum to maximum. For instance, return a result set that gave all the numbers from 7 to 311 (would have 305 rows: 7,8,9...310,311). I am running mysql 3.23.

I have tried several variations on the following:

SELECT @a := 7, @b := 311, @c WHERE ((@c) BETWEEN (@a) AND (@b))

SELECT COUNT(*) as 'c' WHERE (c BETWEEN 7 AND 311)

SELECT @c := COUNT(*) WHERE (@c) BETWEEN 7 AND 311)

SELECT @c WHERE @c IN (7,8,9,10,11,12)

SELECT @myval WHERE BETWEEN(@myval,7,311)

I've also tried implementing some sort of sequentially incrementing variable, like this:

SELECT @c := @c + 1 LIMIT 7,311

HELP! Nothing seems to work, I always get a syntax error returned to me of some form or another. Does anyone know how to do this in a SQL statement?

 

[petersJazz]

This is not the kind of operation a database is intended for. Are the values stored in a table is another thing. Wy do you want it from the an SQL-statement?

 

[shadedecho]

actually, I was generalizing above, I KNOW it sounds like a very simple process in anything other than SQL. What I'm ACTUALLY trying to do is:

I have a table that looks like this:

ID start_date end_date
__ __________ ________
1 2002-01-01 2002-01-16
2 2002-01-17 2002-02-02
3 2002-02-03 2002-02-19
...
? 2002-12-18 2002-12-31

WHAT I WANT TO DO is, based on one record from this table, which defines a start_date (min) and a end_date (max) for a date range, have SQL return a query that has one record for each date in that range, inclusive, so like this:

1 2002-01-17
2 2002-01-18
3 2002-01-19
...
? 2002-02-02

The reason I talked about integers before was because thus far, the best way i've found to deal with these date ranges is to abstract the date into an integer use SQL's builtin "TO_DAYS" and "FROM_DAYS" functions, which change a date to its numeric equivalent of days since the UNIX Epoc (like TO_DAYS('2002-09-16') = 734141). For instance, I grab which record a certain date falls within by doing this:

"SELECT start_date, end_date WHERE TO_DAYS('2002-03-14') BETWEEN TO_DAYS(start_date) AND TO_DAYS(end_date)"

Rather than writing complicated scripts to determine the days in a date range, I am hoping to rely on SQL to do it, and so above, I had generalized my date range into a given range of integers, like 734141 -> 734162. I am theorizing that if I could get SQL to return a result set with a range of numbers from a min and a max that i input into the SQL query, with one number on each row, then I can simply use "FROM_DAYS" to change each integer back into its un-abstracted date form on the outgoing end.

It seems a very simple task to ask SQL to return a result set with a column in each record falling within a certain range. My task is to do so abstractly, in that its not tied necessarily to data in a table. SQL can and is used all the time to perform other non-database dependent functions, like "SELECT POW(3,4)". I simply want to extend its use to return me a set of results, that are related in that they start at a "min", end at a "max", and increment by one. To me, I think this should be able to be accomplished with a powerful language like SQL.

 

[r937]

shadedecho, you have so many ideas floating around, i'm not sure which one(s) i should try to answer, and i'm not really sure where your 734141 type integers might come into this...

so let's start with your query

SELECT start_date, end_date
 WHERE TO_DAYS('2002-03-14') 
       BETWEEN TO_DAYS(start_date) 
           AND TO_DAYS(end_date)

note that you do not have a FROM clause, i'll add that in a moment, and i'll call your date range table "dateranges"

the example you chose was row 2 --

ID  start_date  end_date
2   2002-01-17  2002-02-02

now, let's imagine a table of integers...

create table integers ( i integer );
insert into integers(i) values(0);
insert into integers(i) values(1);
insert into integers(i) values(2);
insert into integers(i) values(3);
insert into integers(i) values(4);
insert into integers(i) values(5);
insert into integers(i) values(6);
etc.

now imagine joining the single row from your date range table, row 2, with as many rows from the integers table as necessary to generate the dates you want

here's how to work this into your query --

SELECT start_date
  FROM dateranges
     , integers
 WHERE TO_DAYS('2002-03-14') 
       BETWEEN TO_DAYS(start_date) 
           AND TO_DAYS(end_date)
   AND start_date + interval i day <= end_date

if you are squeamish about adding an integers table to your database, don't be -- it has hundreds of uses like this

rudy

http://rudy.ca/

 

[petersJazz]

If you want a list of dates in an interval without using a table in a database SQL cant do that in standard SQL. Probably stored procedure in Oracle is possible.

 

[shadedecho]

r937, yes, i didn't put the FROM clause into my select statement, sorry that was an oversight... using the sample date of "2002-09-16", it actually looks like:

"SELECT start_date, end_date FROM dateranges WHERE TO_DAYS('2002-09-16') BETWEEN TO_DAYS(`start_date`) AND TO_DAYS(`end_date`)"

UPDATE:
I did solve this problem in an equally ugly (or rather, less-elegant) fashion as your integer table idea: What I did is make one SQL call, where i got the integer equivalents of the start and end dates (like 734141 and 734162). This would look like the above, only slightly modified:

"SELECT TO_DAYS(`start_date`),TO_DAYS(`end_date`) FROM dateranges WHERE TO_DAYS('2002-09-16') BETWEEN TO_DAYS(`start_date`) AND TO_DAYS(`end_date`)"

This would return me a result set of:

? ?
______ ______
734141 734162

Then, I begin to construct a second SQL statement programattically, with a PHP for-loop from 734141 to 734162, with each iteration of the loop adding a part onto the query string like "FROM_DAYS('$i'),". At the end of that for loop, i have a giant string that looks like this:

"SELECT FROM_DAYS('734141'),FROM_DAYS('734142'),FROM_DAYS('734143'),.....FROM_DAYS('734162')"

Notice, I DO NOT have a FROM or WHERE clause. I'm just using the generic SQL statement, and making use of its builtin functions (TO_DAYS and FROM_DAYS) to do the date conversion work for me. After executing the above statment, I get ONE row, with each column being a respective date:

? ? ?
__________ __________ __________
2002-09-14 2002-09-15 ... 2002-09-25

This gives me my date range without me having to do any thought about how to count days through months and years and such, which is what I was trying to avoid. It also is LESS costly from a overhead standpoint than my original idea because its only 2 SQL statements, and the original idea would have been 1 SQL query, and x# of calls to the db API to retrieve each row from the result set.

I still feel like there ought to be some way to get SQL to return a range of sequentially incremented numbers in successive rows of a result set. If there is, I would REALLY like to have someone tell me how to do it. Otherwise, I'll choose to stay with my ugly but functional solution, and thanks for all the input so far!

 

[r937]

the idea of selecting out of thin air, i.e. without having a FROM clause, is quite handy

some databases do allow it, so if you got it, use it

thanks for mentioning your solution, because i did not realize (i've never needed it) that mysql will allow this too

your php code is probably as ugly as it sounds -- but please note this is not a comment on your coding skills, just on the idea that you would need to write code for something that should be, as you pointed out, simple

since you already have a date range table, why for goodness' sake wouldn't you also have an integers table? it comes up all the time, and it will simplify your php immensely

that's rule #1 -- never do in code what sql can do easily

as for your other request, "I still feel like there ought to be some way to get SQL to return a range of sequentially incremented numbers in successive rows of a result set. If there is, I would REALLY like to have someone tell me how to do it" ...

i could tell you if you really want it, but it involves either (1) writing into a temp table with an autonumber column, then reading that back, or (2) a partial cross join from the table joined to itself, counting how many rows have an equal or lesser value

in other words, inefficient as hell, so the recommendation is to do this in code!

that's rule #2 -- better do in code what sql can't do easily

it is unfortunate that both rules hinge on the definition of "easily," a concept which one does not acquire a good feeling for without lots of database experience

sorry if that sounds self-aggrandizing or commercial (i do sql consulting), but i've seen lots of examples, e.g. code that runs a query, loops through the result set, running a nested query for each row (a simple join would be boatloads better)

rudy

 

[petersJazz]

And maybee another rule

Rule #3 -- use standard SQL or standard portable language as long as possible.

I rather do it in Java than in non standard SQL.