0

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?

csabinho
  • 1,579
  • 1
  • 18
  • 28

1 Answers1

0

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
nbk
  • 45,398
  • 8
  • 30
  • 47
  • You forgot the `INSERT`-part... – csabinho Nov 20 '21 at 18:39
  • Selet Into what? SELECT INTO only works in loops and when you have silge rows. Which you don't have, so what exactly you want to send to ttable into Ort do you mean a Outfile? – nbk Nov 20 '21 at 18:43
  • I need to select from one table into another, but just distinct data. – csabinho Nov 20 '21 at 18:54
  • the inner select select uniwue Name combinations each row is distinct guaranteed by the group by. Soi don't get what you are asking for and why the SELCT INTO idf you want to enter it into another table, non of which you mention in your question, so tka e4 the selct and use it as would you any other – nbk Nov 20 '21 at 18:58