CODEWITHSUNDEEP
mysql
Bastiflew
Bastiflew

Reputation: 1166

MySQL - Generate a counter of xx rows

I just want to generate xx rows with a number to each one, something like that :

SELECT 1 TO 50

The resultset contains 50 rows, numeroted from 1 to 50

thanks

Upvotes: 0

Views: 88

Answers (3)

Franck Dernoncourt
Franck Dernoncourt

Reputation: 83457

With a stored procedure:

delimiter #

create procedure my_counter(lower_bound INT, upper_bound INT)
begin

declare v_max int unsigned default upper_bound;
declare v_counter int unsigned default lower_bound;

CREATE TABLE temp_my_counter (id int);

   while v_counter <= v_max do
    INSERT INTO temp_my_counter SELECT v_counter;   
    set v_counter=v_counter+1;
  end while;

SELECT temp_my_counter.id FROM temp_my_counter;    
DROP TABLE temp_my_counter;

end #    
delimiter ;

Then call my_counter(2, 5); will return 

2
3
4
5

Upvotes: 1

roblovelock
roblovelock

Reputation: 1981

You could try this:

select @count := @count + 1
from (select @count := 0) count
cross join (select 1 from information_schema.TABLES limit 10) as ten
cross join (select 1 from information_schema.TABLES limit 10) as hundred
cross join (select 1 from information_schema.TABLES limit 10) as thousand
cross join (select 1 from information_schema.TABLES limit 10) as ten_thousand
limit 50;

or this (which is quicker):

select @count := @count + 1
from (select @count := 0) count
cross join (select 1 union all select 1) as two
cross join (select 1 union all select 1) as four
cross join (select 1 union all select 1) as eight
cross join (select 1 union all select 1) as sixteen
cross join (select 1 union all select 1) as thirty_two
cross join (select 1 union all select 1) as sixty_four
cross join (select 1 union all select 1) as one_two_eight
cross join (select 1 union all select 1) as two_five_six
cross join (select 1 union all select 1) as five_twelve
cross join (select 1 union all select 1) as one_thousand_twenty_four
limit 50;

Upvotes: 0

ajreal
ajreal

Reputation: 47331

The easiest way :-

select 1 union
select 2 union
select 3 union
select 4 union
...
select 50;

Or, you can consider to write a procedural or user-defined-function

Upvotes: 1

Related Questions