SQL: selecting rows where column value changed from previous row

by 
SELECT a.*
FROM tableX AS a
WHERE a.StatusA <>
      ( SELECT b.StatusA
        FROM tableX AS b
        WHERE a.System = b.System
          AND a.Timestamp > b.Timestamp
        ORDER BY b.Timestamp DESC
        LIMIT 1
      )

But you can try this as well (with an index on (System,Timestamp):

SELECT System, Timestamp, StatusA, StatusB
FROM
  ( SELECT (@statusPre <> statusA AND @systemPre=System) AS statusChanged
         , System, Timestamp, StatusA, StatusB
         , @statusPre := StatusA
         , @systemPre := System
    FROM tableX
       , (SELECT @statusPre:=NULL, @systemPre:=NULL) AS d
    ORDER BY System
           , Timestamp
  ) AS good
WHERE statusChanged ;

More Related Contents:

  1. Recreating a query using INNER JOIN? [duplicate]
  2. Using LIMIT within GROUP BY to get N results per group?
  3. Create a Cumulative Sum Column in MySQL
  4. How to perform grouped ranking in MySQL
  5. Required to join 2 tables with their FKs in a 3rd table
  6. MySQL Trigger after update only if row has changed
  7. MySQL “WITH” clause
  8. mysql select dynamic row values as column names, another column as value
  9. Ordering by specific field value first
  10. MySQL “Group By” and “Order By”
  11. How to change the CHARACTER SET (and COLLATION) throughout a database?
  12. MySQL Query to select data from last week?
  13. SQL – IF EXISTS UPDATE ELSE INSERT INTO
  14. Mysql::Error: Specified key was too long; max key length is 1000 bytes
  15. How can I get around MySQL Errcode 13 with SELECT INTO OUTFILE?
  16. Are there disadvantages to using a generic varchar(255) for all text-based fields?
  17. How do I use properly CASE..WHEN in MySQL
  18. How do I UPDATE a row in a table or INSERT it if it doesn’t exist?
  19. MySQL Select last 7 days
  20. SQL unique varchar case sensitivity question
  21. How do I count columns of a table
  22. Difference between two table structure
  23. Difference between SET autocommit=1 and START TRANSACTION in mysql (Have I missed something?)
  24. How to retrieve JSON data from MySQL?
  25. MySQL equivalent of Oracle’s SEQUENCE.NEXTVAL
  26. Why MySQL is giving error “Not allowed to return a result set from a function”?
  27. When to use a left outer join?
  28. MySQL 5.6 – DENSE_RANK like functionality without Order By
  29. Optimistic locking in MySQL
  30. Insert and set value with max()+1 problems

Leave a Comment