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

My Goal:

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;

CodePudding user response：

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.