CODEWITHSUNDEEP
mysqlsqldistinct
John Abraham
John Abraham

Reputation: 11

Why are duplicate results returned even when distinct keyword is used?

When I run the following query, I am returned two entries with duplicate results. Why are duplicate results returned when I’m using distinct here? The primary keys are the house number, street name, and unit number.

SELECT distinct
  house_num,
  Street_name,
  Unit_Designator,
  Unit_Num
FROM voterinfo.voter_info
WHERE house_num = 420 
  AND street_name = "PARK"
  AND Unit_Num = '' 
  AND Unit_Designator = '';

Upvotes: 1

Views: 1621

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269533

select distinct is a statement that ensures that the result set has no duplicate rows. That is, it filters out rows where every column is the same (and NULL values are considered equal).

It does not look at a subset of columns.

Sometimes, people use select distinct and don't realize that it applies to all columns. It is rather amusing when the first column is in parentheses -- as if parentheses make a difference (they don't).

Then, you might also have situations where values look the same but are not.

Consider this simple example where values differ by only a space as the end of string:

select distinct x
from (select 'a' as x union all
      select 'a '
     ) y;

Here is a db<>fiddle with this example.

This returns two rows, not 1.

Without sample data it is hard to say which of these situations you are referring to. But the rows that you think are "identical" really are not.

Upvotes: 4

VN&#39;sCorner
VN&#39;sCorner

Reputation: 1552

For the fields with datatype as Char or similar ( Street_name,Unit_Designator) it is possible that there are spaces that aren't visible in the query editor that are to be removed by applying appropriate trimming logic.Please refer below link,

MySQL select fields containing leading or trailing whitespace

Upvotes: 0

Related Questions