How to select the most recent dates/time and save data in a new table (SQLite)?

Question

I want to choose the ID with the most recent date with its recent time from Table 1 and then save that new data in a new table called Table 2. I tried using the function offset but did not work. I also saw a similar question (to my question) about using the MAX() function but that code did not work as well. Is there a statement to do that? I appreciate all the help!

Table 1

ID	DATE_1	RUNTIME_1	DATE	ENDTIME_1
1	2021-12-12	10:09:56	2021-12-12	15:09:56
1	2021-12-12	09:09:56	2021-12-12	16:09:56
2	2020-05-22	13:09:12	2021-05-22	22:09:56
2	2020-05-22	09:43:23	2021-05-22	10:09:56
2	2020-05-21	11:43:23	2021-05-22	21:09:56

My goal for Table 2:

ID	DATE_1	RUNTIME_1	DATE	ENDTIME_1
1	2021-12-12	09:09:56	2021-12-12	16:09:56
2	2020-05-22	13:09:12	2021-05-22	22:09:56

My code:

CREATE TABLE table2 AS
SELECT *
FROM table1
WHERE DATE_1>= DATE AND ENDTIME_1>= RUNTIME_1;

Answer 1

DENSE_RANK() with WINDOWING can be used here -

Query -

create table table_2 as 
  with data as 
  (
    select *,  
    dense_rank() over (partition by id order by date_2 || ' '||endtime_1) 
    as etime_rn
    from table1
   )
 select id, date_1, runtime_1, date_2, endtime_1
   from data d
   where etime_rn = (select max(etime_rn) 
      from data d1 where d1.id = d.id 
      group by d1.id);

Refer fiddle here.