DROP TABLE IF EXISTS duplicate_nhs_numbers; CREATE TEMPORARY TABLE duplicate_nhs_numbers ( nhs_number char(10) ); INSERT INTO duplicate_nhs_numbers (nhs_number) SELECT nhs_number FROM patients GROUP BY nhs_number HAVING COUNT(nhs_number) > 1; SELECT p.last_name, p.first_name, p.dob, p.nhs_number FROM patients p JOIN duplicate_nhs_numbers d on d.nhs_number = p.nhs_number ORDER BY p.last_name, p.first_name