Drew Drew - 1 month ago 10
MySQL Question

printing numbers 1 to 100 with 10 numbers per line

In a re-print of a deleted question an hour ago,


if I wanted to print out the numbers 1-100, with 10 numbers to a line
in the mysql shell, how would I go about doing that?

Answer

Generally what i do is create a table (normally a temp table) and populate that with a stored procedure.

CREATE TABLE `numTable` (
  `Id` int(11) NOT NULL auto_increment,
  PRIMARY KEY  (`Id`)
)//

CREATE PROCEDURE dowhile(IN tableLimit INT)
BEGIN
  DECLARE pointer INT DEFAULT tableLimit;
  WHILE pointer > 0 DO
    INSERT numTable VALUES (NULL);
    SET pointer = pointer - 1;
  END WHILE;
END//

CALL dowhile(100)//

now you may need to use DELIMITER but for the sake of consistency i have just copied what worked in SQL Fiddle by setting the Schema Delimiter to be // (forth button bellow the Schema Window)

then from there i then do a select of this table by giving each row a group id. since you want groups of 10 i have set the group to be multiples of 10 and then group by this group id using GROUP_CONCAT to make the rows.

select myRow 
from ( 
  SELECT group_concat(id SEPARATOR ', ') as `myRow`, CEIL(id/10) as `groupId`  
  FROM numTable group by `groupID`) as myTable;

SQL Fiddle

since we don't want to show the group id i then make this a sub-select and only select my new rows. if you use this in something like PHP or C# to output the rows you can just do the one select since you don't have to output everything you get from a query result.