Re: Extracting unique data from tables/views question

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(at)pacific(dot)net(dot)au>
To: "Oliveiros d'Azevedo Cristina" <oliveiros(dot)cristina(at)marktest(dot)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(at)pacific(dot)net(dot)au>
>> To: "Oliveiros d'Azevedo Cristina" <oliveiros(dot)cristina(at)marktest(dot)pt>;
>> "PostgreSQL - newbie" <pgsql-novice(at)postgresql(dot)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
>>