Bart Smissaert <bart.smissa...@gmail.com> wrote:
> 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.
select (select PATIENT_ID from TABLE1 t1
where t1.ADDRESS = t2.ADDRESS
order by DATE_OF_BIRTH limit 1)
from (select distinct ADDRESS from TABLE1) t2;
Igor Tandetnik
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
