CODEWITHSUNDEEP
mysql
mohamed fathi
mohamed fathi

Reputation: 53

Mysql Distinct return all duplicated rows

I am running into this small problem,

when I run this query it works fine, however DISTINCT returns all duplicated values which it shouldn't be, I just wondering why it's acting like that.

Query

SELECT DISTINCT `dstuser` , 
DATE_FORMAT( `t` , '%M %e, %Y' ) AS `date`
FROM `mail`

Tbl

+---------------------+---------+---------+---------+---------+--------+
| time                | srcuser | srchost | dstuser | dsthost | size   |
+---------------------+---------+---------+---------+---------+--------+
| 2006-05-12 15:02:49 | phil    | mars    | phil    | saturn  |   4522 |
| 2006-05-11 10:15:08 | barb    | saturn  | tricia  | mars    |  58274 |
| 2006-05-12 12:48:13 | tricia  | mars    | gene    | venus   | 194925 |
| 2006-05-12 15:02:49 | phil    | mars    | phil    | saturn  |   1048 |
| 2006-05-12 12:48:13 | tricia  | mars    | link    | asure   | 524111 |
+---------------------+---------+---------+---------+---------+--------+

Returns

+---------+--------------+
| dstuser | date         |
+---------+--------------+
| tricia  | May 11, 2006 |
| gene    | May 12, 2006 |
| phil    | May 12, 2006 |
| tricia  | May 13, 2006 |
| barb    | May 14, 2006 |
| tricia  | May 14, 2006 |
| phil    | May 14, 2006 |
| gene    | May 15, 2006 |
| phil    | May 15, 2006 |
| tricia  | May 15, 2006 |
| barb    | May 16, 2006 |
| tricia  | May 17, 2006 |
| gene    | May 19, 2006 |
+---------+--------------+
13 rows in set (0.00 sec)

Upvotes: 0

Views: 32

Answers (1)

Nailgun
Nailgun

Reputation: 4169

DISTINCT returns all unique combinations of dstuser AND date. To avoid duplicated users try to add GROUP BY dstuser

Upvotes: 1

Related Questions