Skip site navigation (1) Skip section navigation (2)

Re: Extracting unique data from tables/views question

From: "Oliveiros d'Azevedo Cristina" <oliveiros(dot)cristina(at)marktest(dot)pt>
To: "Oliveiros d'Azevedo Cristina" <oliveiros(dot)cristina(at)marktest(dot)pt>,"richard terry" <rterry(at)pacific(dot)net(dot)au>
Cc: "postgresql novice" <pgsql-novice(at)postgresql(dot)org>,"Oliveiros" <oliveiros(dot)cristina(at)gmail(dot)com>
Subject: Re: Extracting unique data from tables/views question
Date: 2010-12-29 14:13:54
Message-ID: F98D87FD233C484C9DE268E61FDC2116@marktestcr.marktest.pt (view raw or flat)
Thread:
Lists: pgsql-novice
Also,
I'd also like to see output of
SELECT fk_patient,observation_date, value_numeric
FROM documents.vwobservations
LIMIT 100

just to have an idea of how data is stored on your table

Best,
Oliver

----- Original Message ----- 
From: "Oliveiros d'Azevedo Cristina" <oliveiros(dot)cristina(at)marktest(dot)pt>
To: "richard terry" <rterry(at)pacific(dot)net(dot)au>
Cc: "postgresql novice" <pgsql-novice(at)postgresql(dot)org>; "Oliveiros" 
<oliveiros(dot)cristina(at)gmail(dot)com>
Sent: Wednesday, December 29, 2010 2:08 PM
Subject: Re: [NOVICE] 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(at)pacific(dot)net(dot)au>
> To: "Oliveiros d'Azevedo Cristina" <oliveiros(dot)cristina(at)marktest(dot)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(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
>>>
>
>
> -- 
> Sent via pgsql-novice mailing list (pgsql-novice(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-novice 


In response to

pgsql-novice by date

Next:From: Gurjeet SinghDate: 2010-12-29 14:35:40
Subject: Re: Extracting unique data from tables/views question
Previous:From: Oliveiros d'Azevedo CristinaDate: 2010-12-29 14:08:52
Subject: Re: Extracting unique data from tables/views question

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group