What about doing a little math against your ID column to dynamically generate the group?
SELECT grp, FLOOR(id/10) AS id_grp
FROM animals
GROUP BY grp, id_grp
This would give you groups of 10 based on the ID of the record. I used your animals table above to generate the data below.
Sample data
INSERT INTO animals VALUES
('mammal',10,'dog'),('mammal',11,'dog'),('mammal',12,'dog'),
('mammal',21,'cat'),('mammal',22,'cat'),('mammal',23,'cat'),
('mammal',24,'cat'),('mammal',25,'cat'),('mammal',26,'cat'),
('bird',30,'penguin'),('bird',31,'penguin'),('bird',32,'penguin'),
('bird',33,'penguin'),('fish',44,'lax'),('fish',45,'lax'),
('fish',46,'lax'),('fish',47,'lax'),('fish',48,'lax'),
('mammal',31,'whale'),*'fish',51,'lax'),('fish',52,'lax'),
('fish',53,'lax'),('fish',54,'lax'),('bird',10,'ostrich');
Query Output
+--------+--------+
| grp | id_grp |
+--------+--------+
| fish | 4 |
| fish | 5 |
| mammal | 1 |
| mammal | 2 |
| mammal | 3 |
| bird | 1 |
| bird | 3 |
+--------+--------+
7 rows in set (0.00 sec)
Another way to get the result is this. It finds first all groups that the teacher has surely taught (or is going to) by checking that she has started within the month and then in another subquery it finds - for every group - the last teacher that started at the first day of the month or earlier.
With the unique index you have on the table, the second subquery should be quite efficient. The first subquery would benefit from an index on (teacherid, startdate, groupid)
:
SELECT groupid
FROM pupilgroupteacher
WHERE teacherid = @teacher
AND startdate >= @month + INTERVAL 1 DAY
AND startdate < @month + INTERVAL 1 MONTH
UNION DISTINCT
SELECT gg.groupid
FROM
( SELECT DISTINCT groupid
FROM pupilgroupteacher
) AS gd
JOIN pupilgroupteacher AS gg
ON gg.groupid = gd.groupid
AND gg.startdate =
( SELECT MAX(gi.startdate)
FROM pupilgroupteacher AS gi
WHERE gi.groupid = gd.groupid
AND gi.startdate < @month + INTERVAL 1 DAY
)
WHERE gg.teacherid = @teacher ;
Best Answer
This works, provided the combination
groupid - startdate
is unique: