CODEWITHSUNDEEP
mysqlsubsetself-join
Dave McLelland
Dave McLelland

Reputation: 11

Join to subset of same table

I'm trying to join a table to a subset of itself, and am having trouble. My input contains an ID and a date as follows:

 2013-10-14 eca02319d91421445f82d570960c5d0c

 2013-10-15 eca02319d91421445f82d570960c5d0c

 2013-10-16 eca02319d91421445f82d570960c5d0c

 2013-10-17 eca02319d91421445f82d570960c5d0c

 2013-10-19 eca02319d91421445f82d570960c5d0c

 2013-10-21 eca02319d91421445f82d570960c5d0c

My goal is to determine the last prior date seen for the same id for each date, such as:

     Date          ID                                 Last Date
  2013-10-21    eca02319d91421445f82d570960c5d0c     2013-10-19

  2013-10-19    eca02319d91421445f82d570960c5d0c     2013-10-17
  etc.

Using the following SQL, I get the output below:

select a.Day,a.entity, b.Day from Visitor a
    LEFT OUTER JOIN Visitor b 
on a.entity = b.entity
       and b.day < a.day
where b.day is not null

 output

 2013-10-21 eca02319d91421445f82d570960c5d0c    2013-10-14

 2013-10-21 eca02319d91421445f82d570960c5d0c    2013-10-15

 2013-10-21 eca02319d91421445f82d570960c5d0c    2013-10-17

 2013-10-21 eca02319d91421445f82d570960c5d0c    2013-10-19

 2013-10-19 eca02319d91421445f82d570960c5d0c    2013-10-14

 2013-10-19 eca02319d91421445f82d570960c5d0c    2013-10-15

What is required to get only a single record for each instance of a.day as well as process b.day in descending order?

Thank you in advance!

Upvotes: 1

Views: 1191

Answers (1)

7alhashmi
7alhashmi

Reputation: 924

Try something like this:

SELECT distinct a.date, a.id, (select d.date from data d
        where a.date > d.date AND a.id = d.id
        order by d.date desc limit 1) as last_date 
FROM data a where (select d.date from data d
        where a.date > d.date AND a.id = d.id
        order by d.date desc limit 1) is not null
ORDER by a.date desc;

SQL Fiddle

Upvotes: 1

Related Questions