MySQL 5.

Table: schedule
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

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

  • 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

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
| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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