Selecting dates when column value changes

Question

I have a table like this

| ID | CAMPAIGN_ID | CHARGES |                            DATE |
----------------------------------------------------------------
|  1 |           1 |      15 | November, 17 2012 09:53:08+0000 |
|  2 |           1 |      15 | November, 18 2012 09:53:08+0000 |
|  3 |           1 |      15 | November, 22 2012 09:53:08+0000 |
|  4 |           1 |      15 | November, 24 2012 09:53:08+0000 |
|  5 |           1 |      15 | November, 26 2012 09:53:08+0000 |
|  6 |           1 |      10 | November, 27 2012 09:53:08+0000 |
|  7 |           1 |      10 | November, 28 2012 09:53:08+0000 |
|  8 |           1 |      10 | December, 02 2012 09:53:08+0000 |
|  9 |           1 |      10 | December, 04 2012 09:53:08+0000 |
| 10 |           1 |      10 | December, 05 2012 09:53:08+0000 |
| 11 |           1 |      10 | December, 06 2012 09:53:08+0000 |
| 12 |           1 |      10 | December, 07 2012 09:53:08+0000 |
| 13 |           1 |      15 | December, 08 2012 09:53:08+0000 |
| 14 |           1 |      15 | December, 09 2012 09:53:08+0000 |
| 15 |           1 |      15 | December, 10 2012 09:53:08+0000 |
| 16 |           1 |      15 | December, 12 2012 09:53:08+0000 |
| 17 |           1 |      15 | December, 13 2012 09:53:08+0000 |

SQL Fiddle Schema

I want to select the dates on which the charges are being changing. The output is here

| ID | CAMPAIGN_ID | CHARGES |                            DATE |
----------------------------------------------------------------
|  6 |           1 |      10 | November, 27 2012 09:53:08+0000 |
| 13 |           1 |      15 | December, 08 2012 09:53:08+0000 |

How can i achieve this with mysql.

because on id 6 there is a different charges from the previous ones. and same goes with 13 — Muhammad Raheel, May 06 '13 at 13:52
As you can see grouping by charges will not do the trick. It will group 15 which i have already in the starting records — Muhammad Raheel, May 06 '13 at 13:55
Have you checked [this](http://stackoverflow.com/questions/6560000/sql-selecting-rows-where-column-value-changed-from-previous-row)? — user1190992, May 06 '13 at 13:56
@raheelshan in mysql i think there is no previous row, mysql gives result by random order, unless there is Order.... So your expected results can be selected by Ordering? By example : selecting for each row previous date row and comparing charges? you need to describe rules... — Justin, May 06 '13 at 14:13

Denis de Bernardy · Answer 1 · 2013-05-06T14:06:33.350

MySQL has no window functions, but you could do this using a variable. E.g. something like (note: it's probably invalid sql due to dup aliases, but it should put you on the right track):

select ranked_campaigns.id, ranked_campaigns.campaign_id, ranked_campaigns.charges, ranked_campaigns.date
from (
  select id, campaign_id, charges, date, @rank = @rank + 1 as rank
  from ( select @rank := 1) as r,
       ( select id, campaign_id, charges, date
         from campaigns
         order by campaign_id, date ) as raw_campaigns
  ) as ranked_campaigns
join (
  select id, campaign_id, charges, date, @rank = @rank + 1 as rank
  from ( select @rank := 1) as r,
       ( select id, campaign_id, charges, date
         from campaigns
         order by campaign_id, date ) as raw_campaigns
  ) as ranked_campaigns2
on ranked_campaigns.rank = ranked_campaigns2.rank - 1
and ranked_campaigns.campaign_id = ranked_campaigns2.campaign_id
and ranked_campaigns.charges <> ranked_campaigns2.charges;

it is completely out of story. Gives output that is not required. And please use the fiddle for this. — Muhammad Raheel, May 06 '13 at 14:03
no no... you need that not required `rank` output as a criteria for a join as shown in my edit. — Denis de Bernardy, May 06 '13 at 14:05

score 1 · Accepted Answer · edited May 06 '13 at 14:36

Here's another way...

SELECT b.*
  FROM
     ( SELECT x.* 
     , COUNT(*) rank
  FROM campaign_charges x
  JOIN campaign_charges y
    ON y.campaign_id = x.campaign_id
   AND y.id <= x.id
 GROUP
    BY x.campaign_id
     , x.id
      ) a
JOIN     ( SELECT x.* 
     , COUNT(*) rank
  FROM campaign_charges x
  JOIN campaign_charges y
    ON y.campaign_id = x.campaign_id
   AND y.id <= x.id
 GROUP
    BY x.campaign_id
     , x.id
      ) b
     ON b.campaign_id = a.campaign_id AND b.rank = a.rank+1
   AND a.charges <> b.charges;

Selecting dates when column value changes

2 Answers2

SQL Fiddle Demo