Re: Extracting unique data from tables/views question

On Thursday 30 December 2010 01:35:40 Gurjeet Singh wrote:
> 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;
>
Again this won't work because the data in the observations table is as
follows, it ends up in there having been imported as a HL7 message in the
firstplace:

id - date-result
---------------------
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

ie, each patient has hba1c's done regularly between once a year, often twice a
year and sometimes three times  a year, so the the patient key-date-result is
DISTINCT hence multiple rows will be returned in the view using your
suggestion.

I did find a way to do it but it was quite slow and looks a logical mess, by
doing an internal query which pulls out all patients who have had a hba1c, and
then to get the latest result, doing an internal query limiting the result to
the latest value for that patient, for both the data and the date for the
requiredd fields:

 SELECT DISTINCT vwobservations.fk_patient,  vwpatients.surname,
vwpatients.firstname, vwpatients.birthdate, vwpatients.age,

  ( SELECT vwobservations.observation_date
           FROM documents.vwobservations
          WHERE vwobservations.fk_patient = vwpatients.fk_patient AND
vwobservations.loinc = '4548-4'
          ORDER BY vwobservations.observation_date DESC
         LIMIT 1) AS observation_date,
 ( SELECT vwobservations.value_numeric
           FROM documents.vwobservations
          WHERE vwobservations.fk_patient = vwpatients.fk_patient AND
vwobservations.loinc = '4548-4'
          ORDER BY vwobservations.observation_date DESC
         LIMIT 1) AS hba1c

   FROM contacts.vwpatients, documents.vwobservations
  WHERE vwobservations.fk_patient = vwpatients.fk_patient AND
vwobservations.loinc = '4548-4'::text
  ORDER BY hba1c;


Must be a better way.

regards

Richard