In postgres there is a built in function generate_series() which can generate rows with number.
is there a function in mysql does same thing?
2
-
Awesome :) If you found a duplicate question that gives you the answer, I would suggest closing this one.Vlad the Impala– Vlad the Impala2009-11-27 02:38:39 +00:00Commented Nov 27, 2009 at 2:38
-
how can i close a question? where is link? i think probably i have no enough reputaion to do it.anru– anru2009-11-27 02:43:05 +00:00Commented Nov 27, 2009 at 2:43
Add a comment
|
3 Answers
try this one:
select @rownum:=@rownum+1 n, t.* from tbl t, (SELECT @rownum:=0) r order by somefield
Comments
If all else fails, you could replicate the function in MySQL as a procedure.
Something like this might work
DELIMITER //
DROP PROCEDURE IF EXISTS `generate_series`//
CREATE PROCEDURE `generete_series`(p_start Int, p_end Int)
BEGIN
/* We need a temporary table to hold the values until we can output them */
CREATE TEMPORARY TABLE `temp_series`( val Int Not Null );
/* Add all the values in the range to the temp table. */
set @insert_query = CONCAT('INSERT INTO `temp_series` VALUES (', p_start, ')');
set @ind = p_start + 1;
while @ind <= p_end do
set @insert_query = CONCAT(@insert_query, ',(', @ind, ')');
set @ind = @ind + 1;
end while;
prepare stmt FROM @insert_query;
execute stmt;
/* Select the values and echo them back. */
SELECT * FROM `temp_series`;
/* Free resources. This isnt needed, technically, unless
* you plan on using the function multiple times per connection */
DROP TABLE `temp_series`;
END//
DELIMITER ;
Note that this is not really a very efficient procedure, as it uses a temporary table and prepared query. Not a good thing to use very frequently.
You should look for alternative methods. There is most likely a better way to do whatever it is you are trying to do, without having to resort to this.
Comments
Or, maybe you mean How do I make a row generator in mysql.
