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

Re: Extracting unique data from tables/views question

From: Gurjeet Singh <singh(dot)gurjeet(at)gmail(dot)com>
To: richard terry <rterry(at)pacific(dot)net(dot)au>
Cc: PostgreSQL - newbie <pgsql-novice(at)postgresql(dot)org>
Subject: Re: Extracting unique data from tables/views question
Date: 2010-12-29 14:35:40
Message-ID: AANLkTinPsWWif1h4FpVoC4fPGNhuR7XfKGFWs95vMfG=@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-novice
On Wed, Dec 29, 2010 at 7:52 AM, 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;
>
>  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
>
>
You might want to use SELECT DISTINCT ON

http://www.postgresql.org/docs/9.0/static/sql-select.html#SQL-DISTINCT

Try your query as:

CREATE OR REPLACE VIEW research.diabetes_all_hba1c_results AS
SELECT DISTINCT ON (vwobservations.fk_patient,
vwObservations.observation_date)
vwobservations.fk_patient, vwObservations.observation_date,
vwobservations.value_numeric
          FROM documents.vwobservations where loinc = '4548-4'
order by fk_patient, observation_date;

HTH,
-- 
gurjeet.singh
@ EnterpriseDB - The Enterprise Postgres Company
http://www.EnterpriseDB.com

singh(dot)gurjeet(at){ gmail | yahoo }.com
Twitter/Skype: singh_gurjeet

Mail sent from my BlackLaptop device

In response to

Responses

pgsql-novice by date

Next:From: richard terryDate: 2010-12-30 02:23:31
Subject: Re: Extracting unique data from tables/views question
Previous:From: Oliveiros d'Azevedo CristinaDate: 2010-12-29 14:13:54
Subject: Re: Extracting unique data from tables/views question

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