Remove duplicate records from a query created in the same day

Question

I am trying to create a SQL query that pulls all unique records created in the same day. Example:

Table 1
Field 1     Field 2         Field 3
Bob         [email protected]   2015-07-15
Rob         [email protected]   2015-07-16
Tiffany     [email protected]   2015-07-15

the result should be:

Field 1     Field 2         Field 3
Bob         [email protected]   2015-07-15
Rob         [email protected]   2015-07-16

I tried to use select distinct, but I need the result to contain all fields. De-duplication should be on email address and it can pull either the first or last entry.

possible duplicate of [Find duplicate records in MySQL](http://stackoverflow.com/questions/854128/find-duplicate-records-in-mysql) — RandomMooCow, Jul 15 '15 at 19:14

score 0 · Answer 1 · answered Jul 15 '15 at 18:15

Although not guaranteed to work, the following probably will do what you want:

select t.*
from table1 t
group by field2, field3;

MySQL does not guarantee that the fields come from the same row. In fact, the documentation is quite clear that the columns come from indeterminate (but matching) rows. In practice, the values do seem to come from the same row.

If you want more control, you can use variables:

select t.*
from (select t.*,
             (@rn := if(@e = email, @rn + 1,
                        if(@e := email, 1, 1)
                       )
             ) as seqnum
      from table1 t cross join
           (select @rn := 0, @e := '') param
      order by email
     ) t
where seqnum = 1;

thank you for your answer! it really helps to have this level of detail for other queries that I have yet to get to. — Amado, Jul 16 '15 at 20:22

score -1 · Answer 2 · answered Jul 15 '15 at 18:16

-1

select *
from table1
group by Field3

answered Jul 15 '15 at 18:16

HashSu

1,507
1
13
13

1

This doesn't do deduplication based on email address (field2) as required by the OP. – Marki555 Jul 16 '15 at 08:12
of course, so simple! thank you, I will try that approach! – Amado Jul 16 '15 at 20:22

Remove duplicate records from a query created in the same day

2 Answers2