Have (simplified) a table like this:
CREATE TABLE TABLE1(
[PATIENT_ID] INTEGER PRIMARY KEY,
[ADDRESS] TEXT,
[DATE_OF_BIRTH] TEXT)
DATE_OF_BIRTH is in the ISO8601 format yyyy-mm-dd
Now I need a SQL to find the oldest patients living at all the
different (unique) addresses, so this will be
the patient with the lowest DATE_OF_BIRTH. I will need the PATIENT_ID
of that patient and nil else.
Seems simple, but I had trouble getting this and eventually came up with:
SELECT T1.PATIENT_ID
FROM TABLE1 T1
WHERE
T1.DATE_OF_BIRTH || T1.ADDRESS IN
(SELECT MIN(T2.DATE_OF_BIRTH) || T2.ADDRESS
FROM
TABLE1 T2
GROUP BY T2.ADDRESS)
This seems to work fine, but I am not sure if this SQL is correct and
if the results will always be correct and have a feeling
that there must be a better construction.
Any suggestions?
RBS
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
