Mysql – A query for a latest version of data

datetimeMySQLstored-proceduresview

MySQL.

Let there is a table with fields startdate and kind.

Actually in the real problem column named here startdate and kind are in a UNIQUE index, but the case if only startdate (not kind) is UNIQUE would be also interesting.

Let a date d is specified. I want to retrieve the data of given kind which corresponds to the latest startdate which is before d.

As far as I understand this cannot be efficiently done with views.

So, should it be done with a stored procedure? I am a novice in the topic of stored procedures. Your code would be welcome.

Best Answer

This is easy.

let @d be the date and @k be the kind :

select *
from table a
where 
  startdate = (select max(b.startdate) 
               from table b 
               where b.startdate < @d
               and b.kind = @k)
  and a.kind = @k
Related Question