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

Admin 1
Viewed 47

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;
date-arithmetic max group-by sqlite sql
1 Answers

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.

user_104988280
Related