why the query show only duplicated rows with 2 rows only?-CodePudding

I need to select all patients files with duplicated id no for example I have 5 files using same id no and 20 files using same id no ,

I tried the following SELECT statement :

SELECT  patient_no  , id_no  , COUNT(*) 
FROM MR_PATIENT_IDS 
GROUP BY patient_no , id_no
HAVING COUNT(*) > 1

but the output show only id_no duplicated 2 times and not show the greater than 2

what is the missing in the query ?

CodePudding user response：

The subquery will include all duplicated id_no. (with multiple patient_no)

Then, you use join to get your list of patient_no id_no.

See db<>fiddle

SELECT t1.patient_no, t1.id_no
FROM MR_PATIENT_IDS t1
INNER JOIN (
  SELECT id_no, COUNT(*) AS count
  FROM MR_PATIENT_IDS 
  GROUP BY id_no
  HAVING COUNT(*) > 1
) t2 ON t1.id_no = t2.id_no

CodePudding user response：

Use the below query to get the duplicate records.

SELECT * FROM MR_PATIENT_IDS WHERE rowid not in (SELECT MIN(rowid) FROM MR_PATIENT_IDS GROUP BY patient_no, id_no);