That is a strange construction and for now I haven't got it to work yet in my VB application. It does run though in Firefox SQLite manager. Maybe after all the SQL I came up with in the end wasn't that bad.
RBS On Wed, Nov 17, 2010 at 12:09 AM, Igor Tandetnik <[email protected]> wrote: > Bart Smissaert <[email protected]> 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 > [email protected] > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > _______________________________________________ sqlite-users mailing list [email protected] http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
