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: "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:02:46
Message-ID: A42F06E903CD4CF2B1057483B09FFC1C@marktestcr.marktest.pt (view raw or flat)
Thread:
Lists: 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(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
>> 


In response to

pgsql-novice by date

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

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