mySQL query select row if given columns value different from previous row

Question

So I have this database table with some records and I would like to only select a row if its different from previous based on few columns ( C1, C2, C3 ) .. ignoring date and all other columns .. I want to check against these 3 columns and bring only a row if its different from previous. Need some help here.

Name    C1  C2  C3  Date
Test    Y   Y   Y   10/23/2017 0:00:00
Test    Y   Y   Y   10/24/2017 0:00:00
Test    N   Y   Y   10/25/2017 0:00:00
Test    Y   Y   N   10/26/2017 0:00:00
Test    Y   Y   N   10/27/2017 0:00:00
Test    Y   Y   Y   10/28/2017 0:00:00
Test    Y   Y   Y   10/30/2017 0:00:00

The result I want is should be like this:

Name    C1  C2  C3  Date
Test    Y   Y   Y   10/23/2017 0:00:00
Test    N   Y   Y   10/25/2017 0:00:00
Test    Y   Y   N   10/26/2017 0:00:00
Test    Y   Y   Y   10/28/2017 0:00:00

why last row in your expected result have the same data as the first row ? — Gagantous
– Gagantous, Commented Oct 26, 2017 at 1:56
@Gagantous everytime a row is different from its immediate previous row, I want to select it. But it does not matter if its same first row or different from first row .. makes sense? — Wali Hassan
– Wali Hassan, Commented Oct 26, 2017 at 2:07
What have you tried? What parts can you do? Please read & act on How to Ask and minimal reproducible example. — philipxy
– philipxy, Commented Oct 26, 2017 at 2:52

Strawberry · Accepted Answer · 2017-10-26 08:52:33Z

2

Assuming you store dates as dates, here's one idea...

SELECT a.*
  FROM my_table a 
  LEFT 
  JOIN 
     ( SELECT x.*
            , MIN(y.date) next 
         FROM my_table x 
         LEFT 
         JOIN my_table y 
           ON y.name = x.name 
          AND y.date > x.date 
        GROUP  
           BY x.Name
            , x.Date
     ) b 
    ON b.Name = a.Name 
   AND b.next = a.date 
   AND b.c1=a.c1 
   AND b.c2=a.c2 
   AND b.c3 = a.c3
 WHERE b.Name IS NULL;

answered Oct 26, 2017 at 8:52

Strawberry

34k14 gold badges43 silver badges57 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Wali Hassan Over a year ago

what is b.next? why b.Name is NULL?

Strawberry Over a year ago

b.next is the next row (when ordered by name and date). NULL filters for ‘next rows’ where a,b and c values are not the same as the previous.

Wali Hassan Over a year ago

oh ok and about AND b.c1=a.c1 AND b.c2=a.c2 AND b.c3 = a.c3 isn't it supposed to be <> instead of = ?

Wali Hassan Over a year ago

of course not .. i read it wrong.. makes sense but does not work. :(

Strawberry Over a year ago

Works just fine

Gordon Linoff · Accepted Answer · 2017-10-26 12:17:13Z

2

I think this does what you want:

select t.*
from t
where (t.c1, t.c2, t.c3) <>
       (select t2.c1, t2.c2, t2.c3
        from t t2
        where t2.name = t.name and t2.date < t.date
        order by t2.date desc
        limit 1
       );

edited Oct 26, 2017 at 12:17

answered Oct 26, 2017 at 2:04

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Comments

Harshal Desai · Accepted Answer · 2017-10-28 07:04:35Z

1

the logic is very simple concatenate all requested columns and rank them and then select

SELECT 
    *
FROM
    (SELECT 
        p.CC,
            p.C1,
            p.C2,
            p.C3,
            p.C4,
            CASE
                WHEN @prev_value = CC THEN @rank_count
                WHEN @prev_value:=CC THEN @rank_count:=@rank_count + 1
            END AS rank
    FROM
        (SELECT 
        CONCAT(C2, C3, C4) AS CC, t.*
    FROM
        mytbl t) p, (SELECT @rank_count:=0, @prev_value:=NULL) r) q
WHERE
    q.rank IS NULL

Thanks. BR, Muhammad.

edited Oct 28, 2017 at 7:04

Harshal Desai

51 bronze badge

answered Oct 28, 2017 at 6:13

Muhammad Ashfaq

111 bronze badge

Collectives™ on Stack Overflow

mySQL query select row if given columns value different from previous row

3 Answers 3

5 Comments

Comments

the logic is very simple concatenate all requested columns and rank them and then select

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

5 Comments

Comments

the logic is very simple concatenate all requested columns and rank them and then select

Comments

Your Answer

Sign up or log in

Post as a guest

Related