I've got a table locations:
user | timestamp | state | other_data
-------------------------------------
1 100 1 some_data
1 200 1 some_data
1 300 0 some_data
1 400 0 some_data
2 100 0 some_data
2 200 0 some_data
This is for a location tracking app. A location has two states (1 for "user is within range" and 0 for "user is out of range").
Now I want to retrieve the last time a user's location state has changed.
Example for user = 1
user | timestamp | state | other_data
-------------------------------------
1 300 0 some_data
Because this was the first location update that has the same state value as the "current" (timestamp 400) record.
Higher-level description: I want to display the user something like "You have been in / out of range since [timestamp]"
The faster the solution, the better of course
I would use ranks to order the rows and then pick the min timestamp of the first ranked rows.
select user,min(timestamp) as timestamp,min(state) as state
from
(select l.*,@rn:=case when @user=user and @state=state then @rn
when @user<>user then 1
else @rn+1 end as rnk
,@user:=user,@state:=state
from locations l
cross join (select @rn:=0,@user:='',@state:='') r
order by user,timestamp desc
) t
where rnk=1
group by user