Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
add comment

2 Answers

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
share|improve this answer
    
Your code does not restrict query only to fields with a specified kind. Please rewrite the query. I suppose that rewriting it in the most trivial way would make it inefficient. Probably we should use LIMIT 1 instead? –  porton Apr 23 '13 at 16:54
    
I edited my question to make clear that we want only data of a given kind. –  porton Apr 23 '13 at 16:55
    
I've updated the query –  druzin Apr 23 '13 at 17:15
add comment

I would like to suggest the following:

SUGGESTION #1 : Query as follows

SET @GivenKind = '...';
SET @GivenDate = d;
SELECT * FROM table WHERE kind=@GivenKind
AND startdate < @GivenDate
ORDER BY startdate DESC LIMIT 1;

SUGGESTION #2 : Define a Good Index

In your question you said

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.

For my suggested query, you will need an index to support the WHERE and ORDER BY

ALTER TABLE table ADD INDEX kind_startdate_ndx (kind,startdate);

This will help the Query Optimizer immensely. Why?

  • kind is a constant in the query
  • startdate is in a range within the kind
  • Order of the columns in the index matters

If this index does not exist, it could result in a range index/table scan or full index/table scan (if the kind's cardinality is too low).

Having an index with startdate,kind will result in a range index scan. If you specify the kind.

Give it a Try !!!

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.