Make selection from mysql group by database with first and last row?

Question

MySQL

There is a period table with records of periods for each subject and group. Unique in it is only the full bundle subject_id - group_id - start (or finish).
It is necessary to select subjects from the table for which at least one of the periods has not ended (i.e. NOW() < finish), but you also need to make a selection of the very first period and the very last one (you can simply use separate fields attached to the current period). Certainly it is desirable to make one request. There is an option to add columns to the table (for example, the numbering of periods for each subject), but so far I do not see a solution in this.

Answer 1

I didn't really understand "There is an option to add columns to the table (for example, the numbering of periods for each subject), but so far I do not see a solution in this."
Without this phrase, you can do this:

select p.*
from periods p
join (
   select subject_id, group_id, min(start) min_start, max(start) max_start, max(case when "your date" between start and finish then start end) mid_start
   from periods 
   group by subject_id, group_id 
   having mid_start is not null  /*если хотите чтобы данные выводились только те у которых "your date" попал в период  */
) temp_T on ( p.subject_id = temp_T.subject_id 
          and p.group_id = temp_T.group_id 
          and p.start in (min_start, coalesce(mid_start,min_start), max_start))

if you don't want Having, you can add mid_start is not null to on.
I don't really understand about intervals, but if they don't intersect, then the solution is fine, if they intersect ... you need to rewrite ;-)
UPD. I re-read the question, if you need NOW() < finish you can change the condition in "your date" between start and finish. But then it is not clear about the maximum interval, do you have them written for the future?
UPD2. If MySQL is 8, you can get by with window functions ;-) it's easier to write