INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Query For Range Values Without Table

Query For Range Values Without Table

(OP)
I'm trying to come up with a query that will give me a range of years from a specific start to specific end, and then to auto-increment the list starting at another specific value. So far I've succeeded partially by getting the year values from an existing table but it does not have the full range needed so is there a way to do this?

I know I can do it in PHP but I really need an actual query to feed into an existing PHP function that expects a query. Here is what I have so far:

CODE --> SQL

SELECT
(@cnt := @cnt + 1) AS ID, `Year`
FROM table_name AS t
CROSS JOIN (SELECT @cnt := '455') AS dummy
WHERE t.ID IS NOT NULL  
GROUP BY t.Year 
ORDER BY t.Year 

What I need as output is something like:

CODE

ID      Year
456     1899
457     1900
458     1901
459     1902
460     1903
and so on 

but what I am getting has the year limited to the existing values while the ID, due to the ORDER BY on Year, is not consecutive.

CODE

ID      Year
543     1937
700     1938
712     1939
726     1940
737     1941 

RE: Query For Range Values Without Table

If you want ID to be Year-1543, then Select `Year`-1543 as ID would be the simplest solution for that aspect.

To fill the gaps use another table with more than the number of rows you need and then

CODE

SELECT @ROW := @ROW + 1 AS ROW
 FROM someOtherTable t
 join (SELECT @ROW := 0) t2
 LIMIT 100; 

And use that as derived table to join to the other result instead of just joining SELECT @cnt := '455'.

Bye, Olaf.

RE: Query For Range Values Without Table

(OP)
Thank you for replying, Olaf. No, I don't want the ID to be the year as the ID needs to start incrementing at a specific value which is being generated by the PHP and is independent of the year. Also, the year needs to be within a range of probably 1899 to 1996. Ideally it shouldn't use a table at all but if that's not possible, then I'll have to rework the function to handle an array as input in addition to being able to handle a query. Perhaps that's the way I'll need to go.

RE: Query For Range Values Without Table

I never said you want the year as the ID. Even if the offset is independant of the year, if the year range is known you know the difference of Id vs year and can use that to compute ID from year for one and then for all rows.

Bye, Olaf.

RE: Query For Range Values Without Table

(OP)
Unfortunately, there is no relationship whatsoever between the ID and the year. In fact, every time the form is submitted, the starting ID will change but the years will not. I have never seen this type of query before so am unsure how to proceed or even if it's possible. The whole point is that it should work with accessing any table which, of course, is an odd thing to try to do in a query but I was hoping it was possible. If it is not, then I'll move on to Plan B of reworking the function to accept an array and do it totally within PHP.

RE: Query For Range Values Without Table

You even define a definate relationship between year and ID. It may not always be th same difference, but since records should have ascending ID with ascending years and no gaps in both of this series, it simply is an outcome of these rules, that there always will be a difference N between ID and year of the result. You just have to precompute N everytime.

The type of query is a simple thing to create numbers, but it depends on any table having at least 100 record, it doesn't matter if that is not in any way related to the main query, it's just contributing the rows and with LIMIT you limit the number of rows. It's just a dervied table you'd join to the main query.

Bye, Olaf.

RE: Query For Range Values Without Table

Quote:

I'm trying to come up with a query that will give me a range of years from a specific start to specific end, and then to auto-increment the list starting at another specific value

You cannot do that in a 'query', as a simple 'query' cannot reference an earlier point or derived value from it's own process.

What you need to use is a "Stored Procedure (SP)" (the SQL 'name' for a function) because a SP can be re-entrant and use values from variables created during the process.

Also does "Without table" mean "without creating a temporary table"?

Chris.

Indifference will be the downfall of mankind, but who cares?
Time flies like an arrow, however, fruit flies like a banana.
Webmaster Forum

RE: Query For Range Values Without Table

(OP)
As this seems an impossibility, thank you all who answered and I'll now move on to Plan B of doing it in PHP and modifying the function to also accept an array in addition to accepting the query.

(Chris, I've always liked the quotes in your signature!)

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close