Query For Range Values Without Table

[PCHomepage]

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:

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:

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.

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

 

[Olaf Doschke]

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

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.

 

[PCHomepage]

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.

 

[Olaf Doschke]

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.

 

[PCHomepage]

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.

 

[Olaf Doschke]

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.

 

[ChrisHirst]

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

 

[PCHomepage]

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!)