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

Re: Extracting unique data from tables/views question

From: richard terry <rterry(at)pacific(dot)net(dot)au>
To: pgsql-novice(at)postgresql(dot)org
Subject: Re: Extracting unique data from tables/views question
Date: 2010-12-30 05:19:15
Message-ID: 201012301619.15643.rterry@pacific.net.au (view raw or flat)
Thread:
Lists: pgsql-novice
On Thursday 30 December 2010 14:08:58 Jasen Betts wrote:

Jasen,

See comments below, thanks heaps.

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

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
 

In response to

pgsql-novice by date

Next:From: Bill P.Date: 2010-12-30 15:44:27
Subject: EnterpriseDB
Previous:From: Jasen BettsDate: 2010-12-30 03:08:58
Subject: Re: Extracting unique data from tables/views question

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