SQL: Select certain rows with window functions in MySQL/MariaDB

My problem can be simplified to the following example:

create table dsort(dfrom date,dto date,pname varchar(10));
insert into dsort(dfrom,dto,pname) values(20180101,20180101,'Anja');
insert into dsort(dfrom,dto,pname) values(20190101,20191231,'Fred');
insert into dsort(dfrom,dto,pname) values(20190201,20190201,'Willy');
insert into dsort(dfrom,dto,pname) values(20190301,20190301,'John');
insert into dsort(dfrom,dto,pname) values(20191230,20200131,'Sepp');
insert into dsort(dfrom,dto,pname) values(20200201,20200202,'Leo');

Now I’m looking for an sql statement which selects all rows (ordered by dfrom) having a dfrom not between the dfrom and dto of any previously selected row, possibly using window functions to get informations on other rows, like this:

select dfrom,dto,pname from ...

resulting in:

dfrom          dto       pname
20180101       20180101  'Anja'
20190101       20191231  'Fred'
20200201       20200202  'Leo'

Can anyone give me the necessary sql statement?

Answer

I think the simplest approach is not exists:

select d.*
from dsort d
where not exists (
    select 1
    from dsort d1
    where d.dfrom > d1.dfrom  and d.dfrom < d1.dto
)

Advertisement

Answer