How can I remove Duplicates row(Distinct doesn't work)?

Question

I've been strugling today Because am trying to remove all the duplicates but it doesn't work

SELECT DISTINCT pays, nom, count(titre) FROM film JOIN personne ON film.idRealisateur = personne.id GROUP BY nom ORDER BY count(titre) DESC LIMIT 10

OUTPUT

Even When I used DISTINCT I got Duplicates in the column pays

Distinctness is based on the columns you have selected. The rows returned are still distinct. — user2260040, Sep 12 '22 at 21:46
All youir data looks distinct to me. Perhaps read the documentation on distinct to understand what it does? — Stu, Sep 12 '22 at 21:46

score 2 · Answer 1 · answered Sep 13 '22 at 04:07

A more clear answer than the comments above: DISTINCT applies to all the columns, not just the first column. In your example, you have three rows with a pays value of "France", but because they have different values of nom, the rows count as distinct rows.

If you want to reduce the result to one row per value of a specific column, then you should use GROUP BY, not DISTINCT.

SELECT pays, count(titre) 
FROM film JOIN personne ON film.idRealisateur = personne.id 
GROUP BY pays 
ORDER BY count(titre) DESC LIMIT 10

I took out nom from this example because if you reduce to one row for the three rows with pays = "France", then what do you want returned as the value of nom? There are three different values to choose from, and MySQL shouldn't make a guess at which one you want. For more explanation on this idea, see my answer to Reason for Column is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause

score 0 · Answer 2 · 2022-09-26T09:17:35.340

As per your table given, it seems like there is a duplicate nom available for the same country. for e.g, As per the first row Pays UK has noms Charlie Chaplin which is the 19th time given in the table as per count(titre).

If you want to remove duplicate rows for a particular column like Pays and noms, we should use Group By with Delete statement.

First of all, perform the query given below.

DELETE FROM films
    WHERE filmID NOT IN
    (
        SELECT MAX(filmID) AS MaxRecordID
        FROM films
        GROUP BY pays,nom
    );

Then perform Group By statement with Select

select pays,nom ,count(*) as totalcount from films Group by pays,nom;

Also, attached screen shot with the result to clarify the questions.

Result with removed duplicate rows

How can I remove Duplicates row(Distinct doesn't work)?

2 Answers2