Mysql Question posted in
The official MySQL documentation can be found here.

How to Select rows in a While Loop in MySQL

I have a table (that represent a matrix) in MySQL as follows:

CREATE TABLE matrices
(
  matrix_id varchar(50),
  `row` INT,
  `column` INT,
  `value` float(24)
);

And I insert following values into the table to define a matrix:

-- INSERT MySQL
INSERT INTO matrices 
(matrix_id, `row`, `column`, `value`)
VALUES
('m1', 1, 1, 1),
('m1', 1, 2, 2),
('m1', 1, 3, 3),
('m1', 2, 1, 4),
('m1', 2, 2, 5),
('m1', 2, 3, 6);

I use the following query to display the matrix in such a way that all the values of a specific row of matrix should appears on first row and for the second row of matrix it should appear on second row

-- loop for printing
SET @counter = 1;
SET @m_id = 'm1';
SET @r = (SELECT max(`row`) from matrices where matrix_id = @m_id);
WHILE @counter <= @r DO
BEGIN
    SELECT GROUP_CONCAT(`value`, SEPARATOR ' ') FROM matrices where
    matrix_id = @m_id and `row` = @r
    SET @counter = @counter + 1
END WHILE;

But It seems I have a issue in my While LOOP for MySQL.

What is the correct syntax of While Loop to use here.
Note: I am using MySQL on onecompiler.com.

I want the output to look like this:

1 2 3
4 5 6
Tags:

2

Answers


  1. 0

    In MySQL, you typically don’t use while loops for row iteration; instead, you leverage SQL’s set-based operations. Here’s a corrected approach to achieve what you want:

    SET @m_id = ‘m1’;
    SET @r = (SELECT max(row) FROM matrices WHERE matrix_id = @m_id);
    SET @counter = 1;

    WHILE @counter <= @r DO
    SELECT GROUP_CONCAT(value ORDER BY column ASC SEPARATOR ‘ ‘)
    FROM matrices
    WHERE matrix_id = @m_id AND row = @counter;
    SET @counter = @counter + 1;
    END WHILE;

    Login or Signup to reply.
  2. 0

    you can try this:

    SELECT GROUP_CONCAT(`value` ORDER BY `column` ASC) as `result`
FROM `matrices`
GROUP BY `row`
ORDER BY `row` ASC;

    Here your sample: click here

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search