Sometimes its necessary to have thousand rows to join or generate new values, we need to get the thousand rows first. For this I used union of numbers 0-9. I have joined four of these to get to four digit values. So here is the code:
SELECT @row := @row + 1 as row FROM (select 0 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) t, (select 0 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) t2, (select 0 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) t3, (select 0 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) t4, (SELECT @row:=0) a
This code displays numbers from 1 to 10000. This is useful for generating dates of a range of dates, which I would soon post about.
#1 by Nando on August 30, 2010 - 8:33 pm
hey there!
Is there a reason that you go through the numbers, 0 to 9 but skip 2 and repeat 6 twice? I’m trying to use your splendid example to get a date range for a specific week but noticed this and was puzzled!
#2 by Srijith R on August 30, 2010 - 9:14 pm
It actually needs only 10 numbers need not be 0-9. Anyways I have corrected it in the code. Thanks for pointing it out