1

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

dv02
  • 333
  • 1
  • 6
  • 17
  • This is the most frequently asked question under this tag. Nevertheless, if you're still struggling, see https://meta.stackoverflow.com/questions/333952/why-should-i-provide-an-mcve-for-what-seems-to-me-to-be-a-very-simple-sql-query – Strawberry Feb 24 '18 at 17:32

3 Answers3

2

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
Vamsi Prabhala
  • 48,685
  • 4
  • 36
  • 58
  • Works like a charm even though quite long... thank you! Will accept in a few days if there aren't any faster or shorter solutions – dv02 Feb 24 '18 at 17:54
1

You can do this with a correlated subquery:

select l.*
from locations l
where l.timestamp = (select max(l2.timestamp)
                     from locations l2
                     where l2.user = l.user
                    );

For this to work well, you want an index on locations(user, timestamp).

This can be faster than the join and group by approach. In particular, the correlated subquery can make use of an index, but a group by on the whole table often does not (in MySQL).

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • This does not work. When I execute it, it returns the `1 | 400 | 0` set instead of `1 | 300 | 0` as in my example result – dv02 Feb 24 '18 at 17:50
0

As far as I am aware the only way to achieve this is a sells join. Something a bit like;

 Select table.* From table
Inner Join
(Select id, max(timestamp) as tm from table group by id) as m
On m.tm = table.timestamp and m.id = table.id

Syntax is for MsSQL, it should transfer to MySQL though. Might have to specify column names instead of table.*

LJ01
  • 589
  • 4
  • 11