How do I get the first n records, per foreign key with MySQL?

Question

I have the following table:

+----+-----------+------+
| id | table2_id | type |
+----+-----------+------+
|  1 |       100 | A    |
|  2 |       100 | B    |
|  3 |       100 | C    |
|  4 |       100 | A    |
|  5 |       250 | A    |
+----+-----------+------+

I need a select statement that would get all the records before the first occurrence of type C, per table2_id. So I want records 1, 2, and 5

I'd do this in code with a loop, but I need to do it in MySQL specifically.

Answer 1

If you are running MySQL 8.0, you can do this with window functions:

select *
from (
    select t.*, 
        min(case when type = 'C' then id end) over(partition by table2_id) min_id
    from mytable t
) t
where min_id is null or id < min_id

In all versions, you could use not exists:

select t.*
from mytable t
where not exists (
    select 1
    from mytable t1
    where t1.table2_id = t.table2_id and t1.id <= t.id and t1.type = 'C'
)