Re: Extracting unique data from tables/views question

Поиск
Список
Период
Сортировка
От Gurjeet Singh
Тема Re: Extracting unique data from tables/views question
Дата
Msg-id AANLkTinPsWWif1h4FpVoC4fPGNhuR7XfKGFWs95vMfG=@mail.gmail.com
обсуждение исходный текст
Ответ на Extracting unique data from tables/views question  (richard terry <rterry@pacific.net.au>)
Ответы Re: Extracting unique data from tables/views question  (richard terry <rterry@pacific.net.au>)
Список pgsql-novice
On Wed, Dec 29, 2010 at 7:52 AM, 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;

 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.gurjeet@{ gmail | yahoo }.com
Twitter/Skype: singh_gurjeet

Mail sent from my BlackLaptop device

В списке pgsql-novice по дате отправления:

Предыдущее
От: "Oliveiros d'Azevedo Cristina"
Дата:
Сообщение: Re: Extracting unique data from tables/views question
Следующее
От: richard terry
Дата:
Сообщение: Re: Extracting unique data from tables/views question