How to use MAX() for multiple dates in SQLite?

My goal is to choose the PTID with the most recent date with its recent time. I was trying to use the MAX() function to choose the most recent date with its recent time but received an error syntax (see the double asterisk on Line 9 in my code). Is there a statement to do that or would it be easier to do it in Python? I appreciate all the help!

Table 1

PTID	RESULT_DATE1	RESULT_TIME	DIAGNOSIS_CD
54	2020-01-06	10:03:02	W34
54	2020-01-01	09:18:05	S38
54	2020-01-01	03:08:45	V98
54	2020-04-04	02:09:08	V98
54	2020-04-04	12:12:34	V89

My Goal:

PTID	RESULT_DATE1	RESULT_TIME	DIAGNOSIS_CD
54	2020-04-04	12:12:34	V98
54	2020-01-06	10:03:02	W34
54	2020-01-01	09:18:05	S38

My Code:

CREATE TABLE covid AS
SELECT t1.*, t2.*
FROM lab9 t1 JOIN diagnosis9 t2 ON t2.PTID = t1.PTID 
AND t1.RESULT_DATE1 || ' ' || t1.RESULT_TIME
BETWEEN 
date(t2.diagdate1, '-7 day') || ' ' || t2.DIAG_TIME
AND 
t2.diagdate1 || ' ' || t2.DIAG_TIME
**WHERE RESULT_DATE1 = MAX(RESULT_DATE1)** 
GROUP BY t1.PTID || DIAGNOSIS_CD 
ORDER BY t1.PTID;

Solution

First, you should not group by the concatenation of 2 columns because this may lead to unexpected results.
You should group by the 2 columns.

Also, you can't use an aggregate function like MAX() in the WHERE clause of a query.

What you need is the max value of the expression t1.RESULT_DATE1 || ' ' || t1.RESULT_TIME which you can finally split to date and time with the functions date() and time():

CREATE TABLE covid AS
SELECT t1.PTID, 
       date(MAX(t1.RESULT_DATE1 || ' ' || t1.RESULT_TIME)) RESULT_DATE1,
       time(MAX(t1.RESULT_DATE1 || ' ' || t1.RESULT_TIME)) RESULT_TIME,
       t2.*
FROM lab9 t1 JOIN diagnosis9 t2 
ON t2.PTID = t1.PTID 
AND t1.RESULT_DATE1 || ' ' || t1.RESULT_TIME
    BETWEEN 
    date(t2.diagdate1, '-7 day') || ' ' || t2.DIAG_TIME AND t2.diagdate1 || ' ' || t2.DIAG_TIME
GROUP BY t1.PTID, t2.DIAGNOSIS_CD 
ORDER BY t1.PTID;

The above query will return the rows with the max datetime for each combination of PTID and DIAGNOSIS_CD with the use if SQLite's feature of bare columns.