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