Search code examples
mysqlprocedure

Adding a new column for numbering in MySQL procedure

I want to make a stored procedure in MySQL that show a data of a table with a row number and not the id of the row. Like this:

enter image description here

And I tried this code:

CREATE DEFINER=`root`@`localhost` PROCEDURE `count_row`()
BEGIN
declare row_num INTEGER;
declare result INTEGER;
SELECT count(id) FROM engineers INTO row_num;
SET result=0;
WHILE(result<=row_num)
DO
SELECT result=result+1, name, date FROM engineers ;
END WHILE;
END

The result was creating undefined number of tabs in MySQL. Then I tried this code:

CREATE DEFINER=`root`@`localhost` PROCEDURE `count_row`()
BEGIN
declare row_num INTEGER;
declare result INTEGER;
SELECT count(id) FROM engineers INTO row_num;
SET result=1;
WHILE(result<=row_num)
DO
SELECT result, name, date FROM engineers;
SET result = result +1;
END WHILE;
END

And I got 5 tabs (equal to total row number) like this:

enter image description here

second tab:

enter image description here

What I want is to show only one tab with one table as shown in the first picture of the question.

Solution

  • Try

    SELECT @row_number:=@row_number+1 as RowNum, name as Engineer, date
FROM engineers, (SELECT @row_number:=0) as t
order by name;

    or

    SET @row_number:=0;
SELECT @row_number:=@row_number+1 as RowNum, name as Engineer, date
FROM engineers
order by name;