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 5.

Table: schedule
Columns:
id  int(10) UN PK AI
startdate   date 
groupid smallint(5) UN

The following statement extracts the Schedule ID for a given group (the schedule with the latest date not greater than the given date):

SELECT id FROM schedule
WHERE groupid=@group AND @date>=startdate
ORDER BY startdate DESC LIMIT 1

Question: Can I extract Schedule IDs for all groups, without enumerating (calling the above statement repeatedly) with all existing group IDs?

share|improve this question
    
Does (groupid) have a unique constraint? Or that UN means unsigned? –  ypercube Oct 4 '13 at 14:30
    
@ypercube: that UN means unsigned. There is a unique index for the pair groupid, startdate. There are no other unique constraints (not counting the primary key for ID) –  porton Oct 4 '13 at 14:36
add comment

1 Answer

up vote 2 down vote accepted

This works, provided the combination groupid - startdate is unique:

SELECT s1.groupid, s1.id FROM schedule s1, (
  SELECT groupid, MAX(startdate) startdate
  FROM schedule
  WHERE @date >= startdate
  GROUP BY groupid
) s2
WHERE s1.groupid = s2.groupid
AND s1.startdate = s2.startdate
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.