Re: Extracting unique data from tables/views question - Mailing list pgsql-novice

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;