On 2010-12-29, 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;
> 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;