CODEWITHSUNDEEP
mysqlsql
Jake
Jake

Reputation: 3486

SELECT DISTINCT not returning distinct values

I have the following MYSQL to get the distinct emails.to values, but see results below

SELECT DISTINCT `emails`.`to`,`emails`.`from` as fromEmail, `emails`.`time` as timestamp
FROM `emails` 
WHERE ((`emails`.`from` = '[email protected]') 
OR (`emails`.`to` = '887')) 
AND (`emails`.`to` != 0) 
ORDER BY `id` DESC LIMIT 4

-

to    fromEmail             timestamp
370   [email protected]  2012-08-15 16:52:15
1923  [email protected]  2012-08-15 10:54:48
890   [email protected]  2012-08-15 10:43:06
890   [email protected]  2012-08-15 10:43:02

Why does it return a repeating 890?

Upvotes: 0

Views: 835

Answers (3)

fancyPants
fancyPants

Reputation: 51868

Because the timestamp differs. The DISTINCT is meant for the whole row, not just one column.

This truncates your timestamp to the date only, then 890 won't repeat. Like suggested by Nikola Markovinović.

SELECT DISTINCT `emails`.`to`,`emails`.`from` as fromEmail, DATE(`emails`.`time`) as timestamp
FROM `emails` 
WHERE ((`emails`.`from` = '[email protected]') 
OR (`emails`.`to` = '887')) 
AND (`emails`.`to` != 0) 
ORDER BY `id` DESC LIMIT 4

If you want to truncate the seconds only, which would also lead to desired result, you could do it like this:

SELECT DISTINCT `emails`.`to`,`emails`.`from` as fromEmail, 
DATE_FORMAT(`emails`.`time`, '%Y-%m-%d %H:%i') as timestamp
FROM `emails` 
WHERE ((`emails`.`from` = '[email protected]') 
OR (`emails`.`to` = '887')) 
AND (`emails`.`to` != 0) 
ORDER BY `id` DESC LIMIT 4

Manual entry for DATE_FORMAT() for further information.

As per your comment, if you want to have only unique emails.to just select that column and no further with DISTINCT.

SELECT DISTINCT `emails`.`to`
FROM `emails` 
WHERE ((`emails`.`from` = '[email protected]') 
OR (`emails`.`to` = '887')) 
AND (`emails`.`to` != 0) 
ORDER BY `id` DESC LIMIT 4

Upvotes: 3

sundar
sundar

Reputation: 1760

SELECT DISTINCT `emails`.`to` 
  FROM `emails`  WHERE ((`emails`.`from` = '[email protected]')  OR (`emails`.`to` = '887'))  AND (`emails`.`to` != 0)  
  ORDER BY `id` DESC LIMIT 4

As per your question, you are looking for distinct to address which you could get it from this query.

Upvotes: 0

Bart Friederichs
Bart Friederichs

Reputation: 33501

As said, truncate to date:

SELECT DISTINCT `emails`.`to`,`emails`.`from` as fromEmail, DATE(`emails`.`time`) as timestamp
FROM `emails` 
WHERE ((`emails`.`from` = '[email protected]') 
OR (`emails`.`to` = '887')) 
AND (`emails`.`to` != 0) 
ORDER BY `id` DESC LIMIT 4

Upvotes: 0

Related Questions