CODEWITHSUNDEEP
mysqlsql
Realizt30
Realizt30

Reputation: 183

Retrieve next row in MySQL

I'm trying to compare whether the string "reject" comes before the string "approve". For example,

+----+----------+
| cid| Status   |
+----+----------+
| 1  | pending  |
| 1  | reject   |
| 1  | pending  |
| 1  | approve  |
| 2  | pending  |
| 2  | reject   |
| 2  | approve  |
| 3  | pending  |
| 3  | approve  |
| 3  | reject   |
| 4  | approve  |
| 4  | pending  |
| 4  | approve  |
+----+----------+

Then from the above table, I would like to return the id 2 because the string approve comes directly after the string reject. My thought so far is to create another column which contain next row value. So after the sql query, it would be like the following: (Note that the id 3 is not included because even though after string reject comes a string approve, but it's not from the same id). 

+----+----------+------------+
| cid| Status   | Next Value |
+----+----------+------------+
| 1  | pending  | reject     |
| 1  | reject   | pending    |
| 1  | pending  | approve    |
| 1  | approve  | pending    |
| 2  | pending  | reject     |
| 2  | reject   | approve    |
| 2  | approve  | pending    |
| 3  | pending  | approve    |
| 3  | approve  | reject     |
| 3  | reject   | approve    |
| 4  | approve  | pending    |
| 4  | pending  | approve    |
| 4  | approve  | (null)     |
+----+----------+------------+

With an idea as above, I'm still not sure on how to write the Query and need help constructing it. I've tried to search but most of the answers are in SQL Server using lag function. Additionally, any other approach would be greatly appreciated. (Note: There's an ID column which represents the number of row 1,2,...,12,etc. Also, there's a column of date, thus, the input status will be in order.

Upvotes: 0

Views: 152

Answers (2)

Paul Spiegel
Paul Spiegel

Reputation: 31772

If your server doesn't support window functions like LAG(), you can use this:

select r.id, r.cid
from mytable r
join mytable a
  on  a.cid = r.cid
  and a.id = (
    select min(id)
    from mytable a2
    where a2.cid = r.cid
      and a2.id  > r.id
  )
where r.Status = 'reject'
  and a.Status = 'approve'

This will return a row with Status = 'reject' which is directly followed by by a row with Status = 'approve'.

db-fiddle

If you only need to know cid without duplicates, then use 

select distinct r.cid

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269463

You can use aggregation and having:

select cid
from t
where status in ('reject', 'approve')
group by cid
having min(case when status = 'reject' then date end) < max(case when status = 'approve' then date end);

This is comparing the earliest reject date to the latest approve date, and returning cids where the reject is before the approve.

Upvotes: 0

Related Questions