Re: Extracting unique data from tables/views question
От | Oliveiros d'Azevedo Cristina |
---|---|
Тема | Re: Extracting unique data from tables/views question |
Дата | |
Msg-id | A42F06E903CD4CF2B1057483B09FFC1C@marktestcr.marktest.pt обсуждение исходный текст |
Ответ на | Extracting unique data from tables/views question (richard terry <rterry@pacific.net.au>) |
Список | pgsql-novice |
Funny thing. My Natural join should have worked then...Unless... Does your table have many repeated (fk_patient,observation_date) pairs? For ex, do you have several hba1c measurements for the same patient on the very same day? For ex, has patient 52, on day 2010-7-2 (the latest on your records) taken several hba1c measurements? If so, we need a way to tell which one is the latest. Best, Oliver N.B.: Please do not forget to include mailing list in CC. It allows someone with more knowledge than me help you in a faster and better way. ----- Original Message ----- From: "richard terry" <rterry@pacific.net.au> To: "Oliveiros d'Azevedo Cristina" <oliveiros.cristina@marktest.pt> Sent: Wednesday, December 29, 2010 1:54 PM Subject: Re: [NOVICE] Extracting unique data from tables/views question > On Thursday 30 December 2010 00:44:35 you wrote: >> Can you show me what this query outputs, please? >> >> SELECT fk_patient,MAX(observation_date) as observation_date >> FROM documents.vwobservations >> WHERE loinc = '4548-4' >> GROUP BY fk_patient > > 52;"2010-07-02" > 53;"2010-07-13" > 62;"2008-10-13" > 65;"2009-01-22" > 90;"2010-09-08" > 115;"2010-04-01" > 120;"2010-02-02" > 135;"2010-11-18" > 168;"2009-07-06" > 194;"2010-09-03" > > This gives me 117 distinct records which I know are correct, but no > actual > hba1c, I guess I need to put it in there somewhere? > > Wish I knew a bit more about sql! > > Regards > > Richard > >> >> Best, >> Oliver >> >> ----- Original Message ----- >> From: "richard terry" <rterry@pacific.net.au> >> To: "Oliveiros d'Azevedo Cristina" <oliveiros.cristina@marktest.pt>; >> "PostgreSQL - newbie" <pgsql-novice@postgresql.org> >> Sent: Wednesday, December 29, 2010 1:37 PM >> Subject: Re: [NOVICE] Extracting unique data from tables/views question >> >> > On Thursday 30 December 2010 00:27:47 you wrote: >> >> SELECT DISTINCT fk_patient, observation_date, value_numeric >> >> FROM documents.vwobservations a >> >> NATURAL JOIN >> >> (SELECT fk_patient,MAX(observation_date) as observation_date >> >> FROM documents.vwobservations >> >> WHERE loinc = '4548-4' >> >> GROUP BY fk_patient) b >> >> ORDER BY fk_patient >> > >> > Runs ok, but gives weird results all the same patient key, and not >> > sure >> > which >> > ones are hba1c. >> > >> > ?? >> > >> > Regards >> > >> > richard >> > >> > 52;"2010-07-02";0.01 >> > 52;"2010-07-02";0.08 >> > 52;"2010-07-02";0.43 >> > 52;"2010-07-02";0.49 >> > 52;"2010-07-02";1 >> > 52;"2010-07-02";2.09 >> > 52;"2010-07-02";2.6 >> > 52;"2010-07-02";2.8 >> > 52;"2010-07-02";3.4 >> > 52;"2010-07-02";3.7 >> > 52;"2010-07-02";3.89 >> > 52;"2010-07-02";4.3 >> > 52;"2010-07-02";4.5 >> > 52;"2010-07-02";5 >> > 52;"2010-07-02";6.3 >> > 52;"2010-07-02";6.6 >> > 52;"2010-07-02";8 >> > 52;"2010-07-02";8.9 >> > 52;"2010-07-02";13.5 >> > 52;"2010-07-02";14.2 >> > 52;"2010-07-02";23 >> > 52;"2010-07-02";25 >> > 52;"2010-07-02";30 >> > 52;"2010-07-02";30.5 >> > 52;"2010-07-02";34 >> > 52;"2010-07-02";38.2 >> > 52;"2010-07-02";45 >> > 52;"2010-07-02";48 >> > 52;"2010-07-02";60 >> > 52;"2010-07-02";68 >> > 52;"2010-07-02";85 >> > 52;"2010-07-02";97 >> > 52;"2010-07-02";100 >> > 52;"2010-07-02";104 >> > 52;"2010-07-02";136 >> > 52;"2010-07-02";138 >> > 52;"2010-07-02";200 >> > 52;"2010-07-02";202 >> > 52;"2010-07-02";212 >> > 52;"2010-07-02";316 >> > 52;"2010-07-02"; >> > 53;"2010-07-13";2.2 >> > 53;"2010-07-13";6.6 >> > 53;"2010-07-13";26 >>
В списке pgsql-novice по дате отправления:
Предыдущее
От: "Oliveiros d'Azevedo Cristina"Дата:
Сообщение: Re: Extracting unique data from tables/views question
Следующее
От: "Oliveiros d'Azevedo Cristina"Дата:
Сообщение: Re: Extracting unique data from tables/views question