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

Re: Extracting unique data from tables/views question

From: Jasen Betts <jasen(at)xnet(dot)co(dot)nz>
To: pgsql-novice(at)postgresql(dot)org
Subject: Re: Extracting unique data from tables/views question
Date: 2010-12-30 03:08:58
Message-ID: ifgt4a$7f3$1@reversiblemaps.ath.cx (view raw or flat)
Thread:
Lists: pgsql-novice
On 2010-12-29, richard terry <rterry(at)pacific(dot)net(dot)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;



In response to

Responses

pgsql-novice by date

Next:From: richard terryDate: 2010-12-30 05:19:15
Subject: Re: Extracting unique data from tables/views question
Previous:From: richard terryDate: 2010-12-30 02:23:31
Subject: Re: Extracting unique data from tables/views question

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