machro machro - 15 days ago 9
MySQL Question

Querying table with dates in SQL

I need to query a MySQL database table that looks like this:

Bike Owner Date
-----------------------
1 Oscar 2014-02-02
2 Oscar 2014-02-02
3 John 2014-04-28
4 Jane 2014-05-29
2 John 2015-04-16
3 Mike 2015-06-16
1 Bob 2015-07-16
4 John 2015-08-16
2 Mike 2016-04-16
3 John 2016-04-16


It contains 4 bikes and as soon as the bike switches from owner, a new record is placed within this table. Since this table doesn't have an 'until' attribute, I am stuck creating a query that gets all the owners of all bikes at a specific date, say 2015-07-06.

Does anyone now how to do this in an easy way? I can think of ways of doing this by creating functions or temporary tables, but this seems way too far fetched for such an easy question.

Thanks!

Answer

One method uses an explicit join and aggregation:

select b.*
from (select bike, max(date) as maxdate
      from bikes b
      where date <= '2015-07-06
      group by bike
     ) bd join
     bikes b
     on b.bike = bd.bike and b.date = bd.maxdate;

A somewhat shorter way to write this:

select b.*
from bikes b
where b.date = (select max(b2.date) from bikes b2 where b2.bike = b.bike);
Comments