Question 200950 in Mysql Question posted in
The official MySQL documentation can be found here.

mysql 5.7, query to achieve this: Get least ID if repeating by message in sequence, order by ID

mysql 5.7, query to achieve this:
Get least ID if repeating by message in sequence, order by ID.

INPUT:

id          message
2485132     Process started
2485118     Process completed
2483660     Process started
2482165     Process started
2480689     Process started
2480686     Process completed
2479225     Process started

OUTPUT:

id          message
2485132     Process started
2485118     Process completed
2480689     Process started
2480686     Process completed
2479225     Process started
Tags:

2

Answers


  1. 0 
    SELECT t1.*
FROM table t1
LEFT JOIN table t2 ON t1.id < t2.id
                  AND t1.message <> t2.message
WHERE NOT EXISTS (
    SELECT NULL
    FROM table t3
    WHERE t1.id < t3.id
      AND t3.id < t2.id
    )
    Login or Signup to reply.
  2. 0

    As I understand your question, you want to bring the "first" record in each group of consecutive rows having the same message.

    Here is one way to solve it in MySQL 5.x with a correlated subquery:

    select t.*
from mytable t
where not t.message <=> (
    select t1.message from mytable t1 where t1.id < t.id order by t1.id desc limit 1
)
order by id desc

    Basically, the subquery returns the status on the "previsous" row; if it is different from the status on the current row, then we retain the row, else we discard it.

    id message
    2485132 Process started
    2485118 Process completed
    2480689 Process started
    2480686 Process completed
    2479225 Process started

    fiddle

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search