Re: [sqlite] Simple SQL question?

2010-11-17 Thread luuk34 
On 17-11-10 00:17, Petite Abeille wrote:
 select  t1.patient_id
 fromtable1 t1
 join(
  select  table1.address,
  min( table1.date_of_birth ) as date_of_birth
  fromtable1
  group bytable1.address
  )
 as  t2
 joint2.address = t1.address

'join' should be:

ONt2.address = t1.address


 and t2.date_of_birth = t1.date_of_birth

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Simple SQL question?

2010-11-17 Thread Bart Smissaert 
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 itandet...@mvps.org wrote:
 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 -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

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Simple SQL question?

2010-11-17 Thread Bart Smissaert 
Tried your SQL, but it doesn't look right and didn't run. Will see if
I can alter it.

RBS


On Tue, Nov 16, 2010 at 11:17 PM, Petite Abeille
petite.abei...@gmail.com wrote:

 On Nov 16, 2010, at 11:55 PM, Bart Smissaert wrote:

 This seems to work fine,

 Then you are golden :)

 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?

 Nothing very meaningful, but you could rewrite the 'in' clause as a 'join' to 
 avoid all these concatenations, e.g.:

 select  t1.patient_id
 from    table1 t1
 join    (
            select      table1.address,
                        min( table1.date_of_birth ) as date_of_birth
            from        table1
            group by    table1.address
        )
 as      t2
 join    t2.address = t1.address
 and     t2.date_of_birth = t1.date_of_birth


 ___
 sqlite-users mailing list
 sqlite-users@sqlite.org
 http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Simple SQL question?

2010-11-17 Thread Bart Smissaert 
What do you suggest should be the full SQL then?

RBS

On Wed, Nov 17, 2010 at 8:16 AM, luuk34 luu...@gmail.com wrote:
 On 17-11-10 00:17, Petite Abeille wrote:
 select  t1.patient_id
 from    table1 t1
 join    (
              select      table1.address,
                          min( table1.date_of_birth ) as date_of_birth
              from        table1
              group by    table1.address
          )
 as      t2
 join    t2.address = t1.address

 'join' should be:

 ON    t2.address = t1.address


 and     t2.date_of_birth = t1.date_of_birth

 ___
 sqlite-users mailing list
 sqlite-users@sqlite.org
 http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Simple SQL question?

2010-11-17 Thread luuk34 
On 17-11-10 09:58, Bart Smissaert wrote:
 What do you suggest should be the full SQL then?

select  t1.patient_id
fromtable1 t1
join(
 select  table1.address,
 min( table1.date_of_birth ) as date_of_birth
 fromtable1
 group bytable1.address
 )
as  t2
ON t2.address = t1.address
and t2.date_of_birth = t1.date_of_birth

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Simple SQL question?

2010-11-17 Thread luuk34 
On 17-11-10 10:00, luuk34 wrote:
 On 17-11-10 09:58, Bart Smissaert wrote:
 What do you suggest should be the full SQL then?

 select  t1.patient_id
 fromtable1 t1
 join(
 select  table1.address,
 min( table1.date_of_birth ) as date_of_birth
 fromtable1
 group bytable1.address
 )
 as  t2
 ON t2.address = t1.address
 and t2.date_of_birth = t1.date_of_birth

i forgot the link:
http://www.sqlite.org/syntaxdiagrams.html#join-constraint
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Simple SQL question?

2010-11-17 Thread Bart Smissaert 
Tried your SQL, but it doesn't run.
Will fiddle it and see if I can make it work.

RBS

On Wed, Nov 17, 2010 at 9:00 AM, luuk34 luu...@gmail.com wrote:
 On 17-11-10 09:58, Bart Smissaert wrote:
 What do you suggest should be the full SQL then?

 select  t1.patient_id
 from    table1 t1
 join    (
             select      table1.address,
                         min( table1.date_of_birth ) as date_of_birth
             from        table1
             group by    table1.address
         )
 as      t2
 ON     t2.address = t1.address
 and     t2.date_of_birth = t1.date_of_birth

 ___
 sqlite-users mailing list
 sqlite-users@sqlite.org
 http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Simple SQL question?

2010-11-16 Thread Bart Smissaert 
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 -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

Re: [sqlite] Simple SQL question?

2010-11-16 Thread Petite Abeille 

On Nov 16, 2010, at 11:55 PM, Bart Smissaert wrote:

 This seems to work fine,

Then you are golden :)

 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?

Nothing very meaningful, but you could rewrite the 'in' clause as a 'join' to 
avoid all these concatenations, e.g.:

select  t1.patient_id
fromtable1 t1
join(
select  table1.address,
min( table1.date_of_birth ) as date_of_birth
fromtable1
group bytable1.address
) 
as  t2
joint2.address = t1.address
and t2.date_of_birth = t1.date_of_birth


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Simple SQL question?

2010-11-16 Thread Igor Tandetnik 
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 -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