CODEWITHSUNDEEP
mysqlsql
Sagar Gangwal
Sagar Gangwal

Reputation: 7937

Want distinct consecutive row

I have below table STORAGE_CAPACITY with me.

CREATE TABLE STORAGE_CAPACITY(DATE_TIME DATETIME,COL1 INT,COL2 INT,COL3 INT,COL4 INT);

INSERT INTO STORAGE_CAPACITY values(SYSDATE(),1,2,3,4);
INSERT INTO STORAGE_CAPACITY values(SYSDATE(),1,2,3,4);
INSERT INTO STORAGE_CAPACITY values(SYSDATE(),4,5,6,7);
INSERT INTO STORAGE_CAPACITY values(SYSDATE(),4,5,8,9);
INSERT INTO STORAGE_CAPACITY values(SYSDATE(),1,2,3,4);

SELECT * FROM storage_capacity

Now what i want is if two consecutive rows have same element in col1 to col4 then i only want older one.

And if same row happened in future then i want that row.

So my expected O/P

 DATE_TIME, COL1, COL2, COL3, COL4
'2017-08-16 16:37:02', '1', '2', '3', '4'
'2017-08-16 16:37:18', '4', '5', '6', '7'
'2017-08-16 16:37:26', '4', '5', '8', '9'
'2017-08-16 16:37:57', '1', '2', '3', '4'

Upvotes: 2

Views: 81

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269443

Assuming that the first column defines the ordering, you can use variables for this:

select t.*
from (select t.*,
             (@rn := if(@cols = concat_ws(',', col1, col2, col3, col4), @rn + 1,
                        if(@cols := concat_ws(',', col1, col2, col3, col4), 1, 1)
                       )
             ) as rn                    
      from t cross join
           (select @cols := '', @rn := 0) params
      order by t.date_time
     ) t
where rn = 1;

Note: To establish the order of insert, it is safer to use an auto_increment columns rather than a datetime column. Multiple rows can be inserted in the table with the same datetime value.

Upvotes: 1

Related Questions