Hi Christian,

Each group of duplicates has set of RecIDs delimited by comma:

SELECT * FROM

( SELECT COUNT(*) AS cnt, GROUP_CONCAT( RecID ) FROM tableX GROUP BY A, B, C )

WHERE cnt > 1

Is that what you need?

Hello Sergey

I have 100'000 records containing some duplicates.
Duplicates are defined by equal values in columns A, B, C and actually D.
The Value of column RecID of course is different.
I can use DISTINCT or GROUP BY to get only one of the duplicated records.

I am looking for a solution to fix this problem by either delete the duplicates or select only the one of the duplicated records.
Deleting is always bad so I was thinking to select only one of the records.
My problem is, to select DISTINCT I have to check columns A, B, C, D but in the result I need column RecID.
(Kernel error: 0x42508. In the SELECT clause you can use only fields listed in the GROUP BY and aggregative functions)
Is this possible? How?

Hi Christian,

That is possible, but do you need to run it regularly?
Or just once?

That's how it works for me - select only the first RecID in group.

SELECT FIRST( RecID ) FROM tableX GROUP BY A, B, C;

Hello Sergey

Thank you, looks good.
I thought to run this regularly.
Is this a problem?
To get a specific record the query needs 9 ms on a mac mini testserver.

No problem.
I thought it can be slow on a large dataset.

That is great – Thank you again!