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

Anteste 0
Viewed 43

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

enter image description here

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

mysql sql
1 Answers

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

Bill Karwin0
Related