How to use autoincrement for a SELECT INTO of a GROUP BY result in MySQL?

Question

I found this

SET @row_number = 0; 
SELECT 
    (@row_number:=@row_number + 1) AS num, 
    firstName, 
    lastName
FROM
    employees
ORDER BY firstName, lastName

solution here for adding row numbers to a MySQL result, but it doesn't work for a GROUP BY-result, as the row number is not continuous, but "jumps" by the amount of rows that are grouped. Or is there a specific solution for SELECT INTO, if there is already an auto increment column?

Answer 1

It is basically the same, you nee dfirst to GROUP By and then Makes the row_number

SET @row_number = 0; 
SELECT 
    (@row_number:=@row_number + 1) AS num, firstName, lastName
FROM
    (SELECT 
        firstName, lastName
    FROM
        employees
    GROUP BY firstName , lastName) t1
ORDER BY firstName , lastName

With MySQL8.x this gets a lot easier.

I am not really sure what you mean by SELECT INTO

But i think you mean an outfile, as you have4 multiple values

SET @row_number = 0; 
SELECT 
(@row_number:=@row_number + 1) AS num, firstName, lastName
    INTO OUTFILE '/tmp/result.txt' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n'
 FROM
    (SELECT 
        firstName, lastName
    FROM
        employees
    GROUP BY firstName , lastName) t1
ORDER BY firstName , lastName

If you want the selected data insert into another table thsi would look like

INSERT INTO table2
SELECT 
(@row_number:=@row_number + 1) AS num, firstName, lastName
    INTO OUTFILE '/tmp/result.txt' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n'
 FROM
    (SELECT 
        firstName, lastName
    FROM
        employees
    GROUP BY firstName , lastName) t1 , (SELECT @row_number = 0) t2
ORDER BY firstName , lastName