Extract data for all groups in a single SELECT statement

Question

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?

Answer 1

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