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.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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?

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
up vote 2 down vote accepted

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

SELECT s1.groupid, 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

Your Answer


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.