Re: Extracting unique data from tables/views question

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