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 |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
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;
From | Date | Subject | |
---|---|---|---|
Next Message | richard terry | 2010-12-30 05:19:15 | Re: Extracting unique data from tables/views question |
Previous Message | richard terry | 2010-12-30 02:23:31 | Re: Extracting unique data from tables/views question |