Re: Extracting unique data from tables/views question

OK, it is almost 2pm in PT and I hadn't lunch yet, I am starving :-)

Can you post the output of this last query you tried before goin to sleep?

Many thanks!

Best,
Oliver

----- Original Message -----
From: "richard terry" <rterry@pacific.net.au>
To: "Oliveiros d'Azevedo Cristina" <oliveiros.cristina@marktest.pt>
Sent: Wednesday, December 29, 2010 1:58 PM
Subject: Re: [NOVICE] Extracting unique data from tables/views question


> On Thursday 30 December 2010 00:44:35 you wrote:
>
> Ok this:
>
> SELECT fk_patient,MAX(observation_date) as observation_date, value_numeric
> FROM documents.vwobservations
> WHERE loinc = '4548-4'
> GROUP BY fk_patient, value_numeric
>
> gives me the values, but I end up with nearly 300 records as patient keys
> are
> duplicated.
>
> ??
>
> Getting pretty tired as its nearly 1am in AU, so might head off to bed,
> I'll
> pick up my email in the morning, thanks for helping me
>
> Regards
>
> richard
>
>
>
>> 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
>>
>> 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
>>