SQLize | PHPize | SQLtest

create table UserActivity( username varchar(20), activity varchar(20), startDate Date, endDate Date ); insert into UserActivity values ('Alice', 'Travel', '2020-02-12', '2020-02-20') ,('Alice','Dancing','2020-02-21', '2020-02-23') ,('Alice','Travel', '2020-02-24', '2020-02-28') ,('Bob','Travel', '2020-02-11', '2020-02-18') , ('Bob','Travel', '2020-02-11', '2020-03-18'); with cte as ( select * ,row_number() over(partition by username order by enddate) as rn from Useractivity) , cte2 as (select username,count(username) as cnt from cte group by username) select cte.* from cte inner join cte2 on cte.username=cte2.username where (cte2.cnt=1 and cte.rn=1) or(cte2.cnt>1 and cte.rn=2); with cte as ( select * ,count(1) over(partition by username ) as cnt, row_number() over(partition by username order by enddate) as rn from useractivity) select * from cte where cnt =1 or rn=2; with cte as(select * ,count(1) over(partition by username ) as cnt, row_number() over(partition by username order by enddate) as rn from useractivity) select * from cte;/* where cnt= 1 and rn=1