CODEWITHSUNDEEP
mysql
Wali Hassan
Wali Hassan

Reputation: 480

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

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

Upvotes: 0

Views: 631

Answers (3)

Muhammad Ashfaq
Muhammad Ashfaq

Reputation: 11

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.

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269483

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
       );

Upvotes: 2

Strawberry
Strawberry

Reputation: 33935

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;

Upvotes: 2

Related Questions