1

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?

2
  • Does (groupid) have a unique constraint? Or that UN means unsigned? Oct 4, 2013 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, 2013 at 14:36

1 Answer 1

2

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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