php - MySQL - query for duplicates of a column and return both original and duplicate rows -

i have table use store systematically chosen "serial numbers" each product bought...

the problem is, csv uploaded believe contained duplicate "serial numbers", means when application tries modify row, may not modifying correct one.

i need able query database , rows double of serial_number column. should this:

id, serial_number, meta1, meta2, meta3 3, 123456, 0, 2, 4 55, 123456, 0, 0, 0 6, 345678, 0, 1, 2 99, 345678, 0, 1, 2 

so can see, need able see both original row , duplicate row , of it's columns of data ... can compare them , determine data inconsistent.

some versions of mysql implement in subquery inefficiently. safe alternative join:

select t.* t join      (select serial_number, count(*) cnt       t       group serial_number      ) tsum      on tsum.serial_number = t.serial_number , cnt > 1 order t.serial_number; 

another alternative use exists clause:

select t.* t exists (select * t t2 t2.serial_number = t.serial_number , t2.id <> t.id) order t.serial_number; 

both these queries (as 1 proposed @fthiella) standard sql. both benefit index on (serial_number, id).