CODEWITHSUNDEEP
mysqlsql
dzona
dzona

Reputation: 3751

MySQL Group by consecutive values

I have rate_history table, with data for each employee and his rate changes over job positions. I want to get job position changes for each employee in single query. Selected job is one with most current update in consecutive rows. Note, same job position can be re assigned to an employee over time.

id job_id updated
1    1    01-01-2015
2    1    01-02-2015
3    2    01-01-2015
4    2    01-03-2015
5    2    01-02-2015
6    1    01-01-2015

result should be:

id job_id updated
1    1    01-01-2015
3    2    01-01-2015
6    1    01-02-2015

Upvotes: 2

Views: 1998

Answers (1)

Santhosh
Santhosh

Reputation: 1791

okay, here is my fiddle link based on my understanding on the output you provided,

You can use row number(not available with mysql but using roundabout) and then getting records which are having job_ids not same as previous records.

Fiddle Link

select t1.id
     , t1.job_id
     , t1.updatedate
     , t2.rn as rnt2
  from temprwithrn as t1
  left 
  join temprwithrn as t2
    on t1.rn = t2.rn + 1
 where t1.job_id <> t2.job_id or t2.job_id is null

Upvotes: 4

Related Questions