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