Обсуждение: Extracting unique data from tables/views question

This question will be a little vague I suspect as I don't know what I'm doing,
but, there is some data below (actually is diabetic Hba1c data, I've copied
from the fields in a view I've constructed):

The view is:

CREATE OR REPLACE VIEW research.diabetes_all_hba1c_results AS
 SELECT DISTINCT vwobservations.fk_patient, vwObservations.observation_date,
vwobservations.value_numeric
           FROM documents.vwobservations where loinc = '4548-4'
order by fk_patient, observation_date;

 and  these are the fields and a little of the data:

fk_patient:observation_date:hba1c


52;"2009-02-06";5.8
52;"2010-07-02";6.6
53;"2009-01-22";7.2
53;"2009-07-28";6.7
53;"2009-12-01";6.8
53;"2010-07-13";6.6
62;"2008-10-13";7.3
65;"2009-01-22";6.7
90;"2010-09-08";6.1
115;"2008-10-27";6.8
115;"2009-02-12";6.7
115;"2009-08-04";6.3
115;"2010-04-01";6.2
120;"2010-02-02";7.1
135;"2010-11-18";6.3
168;"2009-07-06";5.4
194;"2010-01-29";7.3
194;"2010-09-03";6.2


You will note that there are mutliple measurements for each person, on
particular dates, wheas what I want is only the latest value - ie the latest
date.

Any ideas/advice appreciated

Regards

Richard

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

Howdy, Richard.

Can you please give this query a try and see if it outputs the results as
you want them showed?
If it does, you can use it for your view.
If it doesn't (which is possible, because it is untested code) tell me what
went wrong (errors, etc)

Best,
Oliver

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



----- Original Message -----
From: "richard terry" <rterry@pacific.net.au>
To: "PostgreSQL - newbie" <pgsql-novice@postgresql.org>
Sent: Wednesday, December 29, 2010 12:52 PM
Subject: [NOVICE] Extracting unique data from tables/views question


> This question will be a little vague I suspect as I don't know what I'm
> doing,
> but, there is some data below (actually is diabetic Hba1c data, I've
> copied
> from the fields in a view I've constructed):
>
> The view is:
>
> CREATE OR REPLACE VIEW research.diabetes_all_hba1c_results AS
> SELECT DISTINCT vwobservations.fk_patient,
> vwObservations.observation_date,
> vwobservations.value_numeric
>           FROM documents.vwobservations where loinc = '4548-4'
> order by fk_patient, observation_date;
>
> and  these are the fields and a little of the data:
>
> fk_patient:observation_date:hba1c
>
>
> 52;"2009-02-06";5.8
> 52;"2010-07-02";6.6
> 53;"2009-01-22";7.2
> 53;"2009-07-28";6.7
> 53;"2009-12-01";6.8
> 53;"2010-07-13";6.6
> 62;"2008-10-13";7.3
> 65;"2009-01-22";6.7
> 90;"2010-09-08";6.1
> 115;"2008-10-27";6.8
> 115;"2009-02-12";6.7
> 115;"2009-08-04";6.3
> 115;"2010-04-01";6.2
> 120;"2010-02-02";7.1
> 135;"2010-11-18";6.3
> 168;"2009-07-06";5.4
> 194;"2010-01-29";7.3
> 194;"2010-09-03";6.2
>
>
> You will note that there are mutliple measurements for each person, on
> particular dates, wheas what I want is only the latest value - ie the
> latest
> date.
>
> Any ideas/advice appreciated
>
> Regards
>
> Richard
>
>
>
>
> --
> Sent via pgsql-novice mailing list (pgsql-novice@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-novice

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
>
> --
> Sent via pgsql-novice mailing list (pgsql-novice@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/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
>>

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
>>

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.cristina@marktest.pt>
To: "richard terry" <rterry@pacific.net.au>
Cc: "postgresql novice" <pgsql-novice@postgresql.org>; "Oliveiros"
<oliveiros.cristina@gmail.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@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
>>>
>
>
> --
> Sent via pgsql-novice mailing list (pgsql-novice@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-novice

On Thursday 30 December 2010 01:35:40 Gurjeet Singh wrote:
> CREATE OR REPLACE VIEW research.diabetes_all_hba1c_results AS
> SELECT DISTINCT ON (vwobservations.fk_patient,
> vwObservations.observation_date)
> vwobservations.fk_patient, vwObservations.observation_date,
> vwobservations.value_numeric
>           FROM documents.vwobservations where loinc = '4548-4'
> order by fk_patient, observation_date;
>
Again this won't work because the data in the observations table is as
follows, it ends up in there having been imported as a HL7 message in the
firstplace:

id - date-result
---------------------
52;"2009-02-06";5.8
52;"2010-07-02";6.6
53;"2009-01-22";7.2
53;"2009-07-28";6.7
53;"2009-12-01";6.8
53;"2010-07-13";6.6
62;"2008-10-13";7.3
65;"2009-01-22";6.7
90;"2010-09-08";6.1
115;"2008-10-27";6.8
115;"2009-02-12";6.7
115;"2009-08-04";6.3
115;"2010-04-01";6.2

ie, each patient has hba1c's done regularly between once a year, often twice a
year and sometimes three times  a year, so the the patient key-date-result is
DISTINCT hence multiple rows will be returned in the view using your
suggestion.

I did find a way to do it but it was quite slow and looks a logical mess, by
doing an internal query which pulls out all patients who have had a hba1c, and
then to get the latest result, doing an internal query limiting the result to
the latest value for that patient, for both the data and the date for the
requiredd fields:

 SELECT DISTINCT vwobservations.fk_patient,  vwpatients.surname,
vwpatients.firstname, vwpatients.birthdate, vwpatients.age,

  ( SELECT vwobservations.observation_date
           FROM documents.vwobservations
          WHERE vwobservations.fk_patient = vwpatients.fk_patient AND
vwobservations.loinc = '4548-4'
          ORDER BY vwobservations.observation_date DESC
         LIMIT 1) AS observation_date,
 ( SELECT vwobservations.value_numeric
           FROM documents.vwobservations
          WHERE vwobservations.fk_patient = vwpatients.fk_patient AND
vwobservations.loinc = '4548-4'
          ORDER BY vwobservations.observation_date DESC
         LIMIT 1) AS hba1c

   FROM contacts.vwpatients, documents.vwobservations
  WHERE vwobservations.fk_patient = vwpatients.fk_patient AND
vwobservations.loinc = '4548-4'::text
  ORDER BY hba1c;


Must be a better way.

regards

Richard

On 2010-12-29, richard terry <rterry@pacific.net.au> wrote:
> This question will be a little vague I suspect as I don't know what I'm doing,
> but, there is some data below (actually is diabetic Hba1c data, I've copied
> from the fields in a view I've constructed):
>
> The view is:
>

> CREATE OR REPLACE VIEW research.diabetes_all_hba1c_results AS
>  SELECT DISTINCT vwobservations.fk_patient, vwObservations.observation_date,
> vwobservations.value_numeric
>            FROM documents.vwobservations where loinc = '4548-4'
> order by fk_patient, observation_date;

> You will note that there are mutliple measurements for each person, on
> particular dates, wheas what I want is only the latest value - ie the latest
> date.

somewhat vague there, but I gather from opther posts that each patient
may be represented sevreal times with different dates on each row
and you want the row with the newest date.

> Any ideas/advice appreciated

 DISTINCT ON(vwobservations.fk_patient )  ... ORDER BY ... observation_date desc;

like this (untested):

 CREATE OR REPLACE VIEW research.diabetes_all_hba1c_results AS
  SELECT DISTINCT ON(vwobservations.fk_patient ) vwobservations.fk_patient,
 vwObservations.observation_date,
 vwobservations.value_numeric
            FROM documents.vwobservations where loinc = '4548-4'
 order by fk_patient, observation_date desc;

On Thursday 30 December 2010 14:08:58 Jasen Betts wrote:

Jasen,

See comments below, thanks heaps.

> On 2010-12-29, richard terry <rterry@pacific.net.au> wrote:
> > This question will be a little vague I suspect as I don't know what I'm
> > doing, but, there is some data below (actually is diabetic Hba1c data,
> > I've copied from the fields in a view I've constructed):
> >
> > The view is:
> >
> >
> > CREATE OR REPLACE VIEW research.diabetes_all_hba1c_results AS
> >  SELECT DISTINCT vwobservations.fk_patient,
> > vwObservations.observation_date, vwobservations.value_numeric
> >            FROM documents.vwobservations where loinc = '4548-4'
> > order by fk_patient, observation_date;
> >
> > You will note that there are mutliple measurements for each person, on
> > particular dates, wheas what I want is only the latest value - ie the
> > latest date.
>
> somewhat vague there, but I gather from opther posts that each patient
> may be represented sevreal times with different dates on each row
> and you want the row with the newest date.
>
> > Any ideas/advice appreciated
>
>  DISTINCT ON(vwobservations.fk_patient )  ... ORDER BY ... observation_date
>  desc;
>
> like this (untested):
>
>  CREATE OR REPLACE VIEW research.diabetes_all_hba1c_results AS
>   SELECT DISTINCT ON(vwobservations.fk_patient ) vwobservations.fk_patient,
>  vwObservations.observation_date,
>  vwobservations.value_numeric
>             FROM documents.vwobservations where loinc = '4548-4'
>  order by fk_patient, observation_date desc;

Yes, thank, that seems to do the tick, and only a unique and the latest
observation is returned.

Have to head out now, but I'll play around with this tonight.

Thanks to everyone who has contributed to this thread, and to any other
observations, suggestions made/or coming.

Regards

Richard