How to obtain recent date/time using MAX() function if I can not put it in the “where” clause in SQLite?

I am trying to select the recent date and time. I understand that I cannot use MAX() function in the where clause because it will not work. For my table 4, I do not want to use “GROUP BY” statement as I want to see all the ID’s. I tried using this code below but it gave me one row instead of multiple rows from Table 3. If I can not use the MAX() function in the where clause, what other ways can I obtain the most recent date and time?

Table 3:

ID	RESULT_DATE1	RESULT_TIME_1	FINAL_DATE1	FINAL_TIME_1
766	11/13/2020	12:12:12	11/29/2020	13:11:12
766	11/13/2020	12:12:12	11/29/2020	14:11:12
765	10/13/2020	12:12:12	10/14/2020	11:11:12
765	10/20/2021	12:12:12	10/21/2020	12:13:14

Desired table for table4:

ID	RESULT_DATE1	RESULT_TIME_1	FINAL_DATE1	FINAL_TIME_1
766	11/13/2020	12:12:12	11/29/2020	14:11:12
765	10/20/2021	12:12:12	10/21/2020	12:13:14

Code:

CREATE TABLE table4 AS
SELECT *, 
        MAX(RESULT_DATE1)
FROM table3
/*WHERE RESULT_DATE1 || ' ' || RESULT_TIME_1*/
ORDER BY ID 
;

Answer

Here is a simple solution using row_number.

select ID   
      ,RESULT_DATE1
      ,RESULT_TIME_1
      ,FINAL_DATE1  
      ,FINAL_TIME_1
from  (
       select *
              ,row_number() over (partition by ID order by FINAL_DATE1 desc,FINAL_TIME_1 desc) as rn
       from t
       ) t
where rn = 1

ID	RESULT_DATE1	RESULT_TIME_1	FINAL_DATE1	FINAL_TIME_1
765	10/20/2021	12:12:12	10/21/2020	12:13:14
766	11/13/2020	12:12:12	11/29/2020	14:11:12

Fiddle