MySQL list all duplicates on a specific field and only if day between another field date <= 1

0
votes

I have a table in MySQL "mytable" like this : 

    id    car_num      date  
    -----  --------   -----------
    00001    BBB      2015-09-28
    00002    BBB      2015-10-04
    00003    DDD      2015-10-04
    00004    BBB      2015-10-04
    00005    AAA      2015-10-05
    00006    GGG      2015-10-05
    00007    GGG      2015-10-05
    00009    EEE      2015-10-06
    00010    AAA      2015-10-06
    00011    HHH      2015-10-10
    00012    FFF      2015-10-11
    00013    FFF      2015-10-11
    00014    CCC      2015-10-13

Here's my query to display all duplicates (field car_num) :

    SELECT  a.*
    FROM    mytable a
        INNER JOIN
        (
         SELECT  car_num
         FROM    mytable
         GROUP   BY car_num
         HAVING  COUNT(car_num) >= 2
        ) b ON a.car_num = b.car_num
    ORDER BY car_num ASC

Result is :

     id    car_num      date  
    -----  --------   -----------
    00005    AAA      2015-10-05
    00010    AAA      2015-10-06
    00001    BBB      2015-09-28
    00002    BBB      2015-10-04
    00004    BBB      2015-10-04
    00012    FFF      2015-10-11
    00013    FFF      2015-10-11
    00006    GGG      2015-10-05
    00007    GGG      2015-10-05

Now I want to display duplicates only if there is less or equal than 1 day between date fields for each couple of duplicates and I want to order them by date DESC

What I expect :

     id    car_num      date  
    -----  --------   -----------
    00012    FFF      2015-10-11
    00013    FFF      2015-10-11
    00010    AAA      2015-10-06
    00005    AAA      2015-10-05
    00006    GGG      2015-10-05
    00007    GGG      2015-10-05
    00002    BBB      2015-10-04
    00004    BBB      2015-10-04

Is it possible directly with MySQL query (something with DATEDIFF) ? Any help is appreciated

1
phpmysql
Your description and your sample results are inconsistent. – Gordon Linoff
Sorry wrong copy/paste : MySQL query edited – patrak1
Your expected results have 0 or 1 days between the duplicates. This still contradicts your explanation. – Gordon Linoff
I need only if day is less or equal than 1 day, explanation edited... and sorry for my English (I'm french) – patrak1

1 Answers

0
votes

You can do something like this:

select t.*
from mytable t
where exists (select 1
              from mytable t2
              where t.car_num = t2.car_num and 
                    t.id <> t2.id and
                    abs(datediff(t2.date, t.date)) <= 1
             );