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

My goal for Table 2:

My code:

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

CodePudding user response：

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.