Обсуждение: Extracting unique data from tables/views question
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 Regards Richard
On Thursday 30 December 2010 00:27:47 you wrote: > SELECT DISTINCT fk_patient, observation_date, value_numeric > FROM documents.vwobservations a > NATURAL JOIN > (SELECT fk_patient,MAX(observation_date) as observation_date > FROM documents.vwobservations > WHERE loinc = '4548-4' > GROUP BY fk_patient) b > ORDER BY fk_patient > Runs ok, but gives weird results all the same patient key, and not sure which ones are hba1c. ?? Regards richard 52;"2010-07-02";0.01 52;"2010-07-02";0.08 52;"2010-07-02";0.43 52;"2010-07-02";0.49 52;"2010-07-02";1 52;"2010-07-02";2.09 52;"2010-07-02";2.6 52;"2010-07-02";2.8 52;"2010-07-02";3.4 52;"2010-07-02";3.7 52;"2010-07-02";3.89 52;"2010-07-02";4.3 52;"2010-07-02";4.5 52;"2010-07-02";5 52;"2010-07-02";6.3 52;"2010-07-02";6.6 52;"2010-07-02";8 52;"2010-07-02";8.9 52;"2010-07-02";13.5 52;"2010-07-02";14.2 52;"2010-07-02";23 52;"2010-07-02";25 52;"2010-07-02";30 52;"2010-07-02";30.5 52;"2010-07-02";34 52;"2010-07-02";38.2 52;"2010-07-02";45 52;"2010-07-02";48 52;"2010-07-02";60 52;"2010-07-02";68 52;"2010-07-02";85 52;"2010-07-02";97 52;"2010-07-02";100 52;"2010-07-02";104 52;"2010-07-02";136 52;"2010-07-02";138 52;"2010-07-02";200 52;"2010-07-02";202 52;"2010-07-02";212 52;"2010-07-02";316 52;"2010-07-02"; 53;"2010-07-13";2.2 53;"2010-07-13";6.6 53;"2010-07-13";26
Howdy, Richard. Can you please give this query a try and see if it outputs the results as you want them showed? If it does, you can use it for your view. If it doesn't (which is possible, because it is untested code) tell me what went wrong (errors, etc) Best, Oliver SELECT DISTINCT fk_patient, observation_date, value_numeric FROM documents.vwobservations a NATURAL JOIN (SELECT fk_patient,MAX(observation_date) as observation_date FROM documents.vwobservations WHERE loinc = '4548-4' GROUP BY fk_patient) b ORDER BY fk_patient ----- Original Message ----- From: "richard terry" <rterry@pacific.net.au> To: "PostgreSQL - newbie" <pgsql-novice@postgresql.org> Sent: Wednesday, December 29, 2010 12:52 PM Subject: [NOVICE] Extracting unique data from tables/views question > 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 > > Regards > > Richard > > > > > -- > Sent via pgsql-novice mailing list (pgsql-novice@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-novice
Can you show me what this query outputs, please? SELECT fk_patient,MAX(observation_date) as observation_date FROM documents.vwobservations WHERE loinc = '4548-4' GROUP BY fk_patient Best, Oliver ----- Original Message ----- From: "richard terry" <rterry@pacific.net.au> To: "Oliveiros d'Azevedo Cristina" <oliveiros.cristina@marktest.pt>; "PostgreSQL - newbie" <pgsql-novice@postgresql.org> Sent: Wednesday, December 29, 2010 1:37 PM Subject: Re: [NOVICE] Extracting unique data from tables/views question > On Thursday 30 December 2010 00:27:47 you wrote: >> SELECT DISTINCT fk_patient, observation_date, value_numeric >> FROM documents.vwobservations a >> NATURAL JOIN >> (SELECT fk_patient,MAX(observation_date) as observation_date >> FROM documents.vwobservations >> WHERE loinc = '4548-4' >> GROUP BY fk_patient) b >> ORDER BY fk_patient >> > > Runs ok, but gives weird results all the same patient key, and not sure > which > ones are hba1c. > > ?? > > Regards > > richard > > 52;"2010-07-02";0.01 > 52;"2010-07-02";0.08 > 52;"2010-07-02";0.43 > 52;"2010-07-02";0.49 > 52;"2010-07-02";1 > 52;"2010-07-02";2.09 > 52;"2010-07-02";2.6 > 52;"2010-07-02";2.8 > 52;"2010-07-02";3.4 > 52;"2010-07-02";3.7 > 52;"2010-07-02";3.89 > 52;"2010-07-02";4.3 > 52;"2010-07-02";4.5 > 52;"2010-07-02";5 > 52;"2010-07-02";6.3 > 52;"2010-07-02";6.6 > 52;"2010-07-02";8 > 52;"2010-07-02";8.9 > 52;"2010-07-02";13.5 > 52;"2010-07-02";14.2 > 52;"2010-07-02";23 > 52;"2010-07-02";25 > 52;"2010-07-02";30 > 52;"2010-07-02";30.5 > 52;"2010-07-02";34 > 52;"2010-07-02";38.2 > 52;"2010-07-02";45 > 52;"2010-07-02";48 > 52;"2010-07-02";60 > 52;"2010-07-02";68 > 52;"2010-07-02";85 > 52;"2010-07-02";97 > 52;"2010-07-02";100 > 52;"2010-07-02";104 > 52;"2010-07-02";136 > 52;"2010-07-02";138 > 52;"2010-07-02";200 > 52;"2010-07-02";202 > 52;"2010-07-02";212 > 52;"2010-07-02";316 > 52;"2010-07-02"; > 53;"2010-07-13";2.2 > 53;"2010-07-13";6.6 > 53;"2010-07-13";26 > > -- > Sent via pgsql-novice mailing list (pgsql-novice@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-novice
Funny thing. My Natural join should have worked then...Unless... Does your table have many repeated (fk_patient,observation_date) pairs? For ex, do you have several hba1c measurements for the same patient on the very same day? For ex, has patient 52, on day 2010-7-2 (the latest on your records) taken several hba1c measurements? If so, we need a way to tell which one is the latest. Best, Oliver N.B.: Please do not forget to include mailing list in CC. It allows someone with more knowledge than me help you in a faster and better way. ----- Original Message ----- From: "richard terry" <rterry@pacific.net.au> To: "Oliveiros d'Azevedo Cristina" <oliveiros.cristina@marktest.pt> Sent: Wednesday, December 29, 2010 1:54 PM Subject: Re: [NOVICE] Extracting unique data from tables/views question > On Thursday 30 December 2010 00:44:35 you wrote: >> Can you show me what this query outputs, please? >> >> SELECT fk_patient,MAX(observation_date) as observation_date >> FROM documents.vwobservations >> WHERE loinc = '4548-4' >> GROUP BY fk_patient > > 52;"2010-07-02" > 53;"2010-07-13" > 62;"2008-10-13" > 65;"2009-01-22" > 90;"2010-09-08" > 115;"2010-04-01" > 120;"2010-02-02" > 135;"2010-11-18" > 168;"2009-07-06" > 194;"2010-09-03" > > This gives me 117 distinct records which I know are correct, but no > actual > hba1c, I guess I need to put it in there somewhere? > > Wish I knew a bit more about sql! > > Regards > > Richard > >> >> Best, >> Oliver >> >> ----- Original Message ----- >> From: "richard terry" <rterry@pacific.net.au> >> To: "Oliveiros d'Azevedo Cristina" <oliveiros.cristina@marktest.pt>; >> "PostgreSQL - newbie" <pgsql-novice@postgresql.org> >> Sent: Wednesday, December 29, 2010 1:37 PM >> Subject: Re: [NOVICE] Extracting unique data from tables/views question >> >> > On Thursday 30 December 2010 00:27:47 you wrote: >> >> SELECT DISTINCT fk_patient, observation_date, value_numeric >> >> FROM documents.vwobservations a >> >> NATURAL JOIN >> >> (SELECT fk_patient,MAX(observation_date) as observation_date >> >> FROM documents.vwobservations >> >> WHERE loinc = '4548-4' >> >> GROUP BY fk_patient) b >> >> ORDER BY fk_patient >> > >> > Runs ok, but gives weird results all the same patient key, and not >> > sure >> > which >> > ones are hba1c. >> > >> > ?? >> > >> > Regards >> > >> > richard >> > >> > 52;"2010-07-02";0.01 >> > 52;"2010-07-02";0.08 >> > 52;"2010-07-02";0.43 >> > 52;"2010-07-02";0.49 >> > 52;"2010-07-02";1 >> > 52;"2010-07-02";2.09 >> > 52;"2010-07-02";2.6 >> > 52;"2010-07-02";2.8 >> > 52;"2010-07-02";3.4 >> > 52;"2010-07-02";3.7 >> > 52;"2010-07-02";3.89 >> > 52;"2010-07-02";4.3 >> > 52;"2010-07-02";4.5 >> > 52;"2010-07-02";5 >> > 52;"2010-07-02";6.3 >> > 52;"2010-07-02";6.6 >> > 52;"2010-07-02";8 >> > 52;"2010-07-02";8.9 >> > 52;"2010-07-02";13.5 >> > 52;"2010-07-02";14.2 >> > 52;"2010-07-02";23 >> > 52;"2010-07-02";25 >> > 52;"2010-07-02";30 >> > 52;"2010-07-02";30.5 >> > 52;"2010-07-02";34 >> > 52;"2010-07-02";38.2 >> > 52;"2010-07-02";45 >> > 52;"2010-07-02";48 >> > 52;"2010-07-02";60 >> > 52;"2010-07-02";68 >> > 52;"2010-07-02";85 >> > 52;"2010-07-02";97 >> > 52;"2010-07-02";100 >> > 52;"2010-07-02";104 >> > 52;"2010-07-02";136 >> > 52;"2010-07-02";138 >> > 52;"2010-07-02";200 >> > 52;"2010-07-02";202 >> > 52;"2010-07-02";212 >> > 52;"2010-07-02";316 >> > 52;"2010-07-02"; >> > 53;"2010-07-13";2.2 >> > 53;"2010-07-13";6.6 >> > 53;"2010-07-13";26 >>
OK, it is almost 2pm in PT and I hadn't lunch yet, I am starving :-) Can you post the output of this last query you tried before goin to sleep? Many thanks! Best, Oliver ----- Original Message ----- From: "richard terry" <rterry@pacific.net.au> To: "Oliveiros d'Azevedo Cristina" <oliveiros.cristina@marktest.pt> Sent: Wednesday, December 29, 2010 1:58 PM Subject: Re: [NOVICE] Extracting unique data from tables/views question > On Thursday 30 December 2010 00:44:35 you wrote: > > Ok this: > > SELECT fk_patient,MAX(observation_date) as observation_date, value_numeric > FROM documents.vwobservations > WHERE loinc = '4548-4' > GROUP BY fk_patient, value_numeric > > gives me the values, but I end up with nearly 300 records as patient keys > are > duplicated. > > ?? > > Getting pretty tired as its nearly 1am in AU, so might head off to bed, > I'll > pick up my email in the morning, thanks for helping me > > Regards > > richard > > > >> Can you show me what this query outputs, please? >> >> SELECT fk_patient,MAX(observation_date) as observation_date >> FROM documents.vwobservations >> WHERE loinc = '4548-4' >> GROUP BY fk_patient >> >> Best, >> Oliver >> >> ----- Original Message ----- >> From: "richard terry" <rterry@pacific.net.au> >> To: "Oliveiros d'Azevedo Cristina" <oliveiros.cristina@marktest.pt>; >> "PostgreSQL - newbie" <pgsql-novice@postgresql.org> >> Sent: Wednesday, December 29, 2010 1:37 PM >> Subject: Re: [NOVICE] Extracting unique data from tables/views question >> >> > On Thursday 30 December 2010 00:27:47 you wrote: >> >> SELECT DISTINCT fk_patient, observation_date, value_numeric >> >> FROM documents.vwobservations a >> >> NATURAL JOIN >> >> (SELECT fk_patient,MAX(observation_date) as observation_date >> >> FROM documents.vwobservations >> >> WHERE loinc = '4548-4' >> >> GROUP BY fk_patient) b >> >> ORDER BY fk_patient >> > >> > Runs ok, but gives weird results all the same patient key, and not >> > sure >> > which >> > ones are hba1c. >> > >> > ?? >> > >> > Regards >> > >> > richard >> > >> > 52;"2010-07-02";0.01 >> > 52;"2010-07-02";0.08 >> > 52;"2010-07-02";0.43 >> > 52;"2010-07-02";0.49 >> > 52;"2010-07-02";1 >> > 52;"2010-07-02";2.09 >> > 52;"2010-07-02";2.6 >> > 52;"2010-07-02";2.8 >> > 52;"2010-07-02";3.4 >> > 52;"2010-07-02";3.7 >> > 52;"2010-07-02";3.89 >> > 52;"2010-07-02";4.3 >> > 52;"2010-07-02";4.5 >> > 52;"2010-07-02";5 >> > 52;"2010-07-02";6.3 >> > 52;"2010-07-02";6.6 >> > 52;"2010-07-02";8 >> > 52;"2010-07-02";8.9 >> > 52;"2010-07-02";13.5 >> > 52;"2010-07-02";14.2 >> > 52;"2010-07-02";23 >> > 52;"2010-07-02";25 >> > 52;"2010-07-02";30 >> > 52;"2010-07-02";30.5 >> > 52;"2010-07-02";34 >> > 52;"2010-07-02";38.2 >> > 52;"2010-07-02";45 >> > 52;"2010-07-02";48 >> > 52;"2010-07-02";60 >> > 52;"2010-07-02";68 >> > 52;"2010-07-02";85 >> > 52;"2010-07-02";97 >> > 52;"2010-07-02";100 >> > 52;"2010-07-02";104 >> > 52;"2010-07-02";136 >> > 52;"2010-07-02";138 >> > 52;"2010-07-02";200 >> > 52;"2010-07-02";202 >> > 52;"2010-07-02";212 >> > 52;"2010-07-02";316 >> > 52;"2010-07-02"; >> > 53;"2010-07-13";2.2 >> > 53;"2010-07-13";6.6 >> > 53;"2010-07-13";26 >>
Also, I'd also like to see output of SELECT fk_patient,observation_date, value_numeric FROM documents.vwobservations LIMIT 100 just to have an idea of how data is stored on your table Best, Oliver ----- Original Message ----- From: "Oliveiros d'Azevedo Cristina" <oliveiros.cristina@marktest.pt> To: "richard terry" <rterry@pacific.net.au> Cc: "postgresql novice" <pgsql-novice@postgresql.org>; "Oliveiros" <oliveiros.cristina@gmail.com> Sent: Wednesday, December 29, 2010 2:08 PM Subject: Re: [NOVICE] Extracting unique data from tables/views question > OK, it is almost 2pm in PT and I hadn't lunch yet, I am starving :-) > > Can you post the output of this last query you tried before goin to sleep? > > Many thanks! > > Best, > Oliver > > ----- Original Message ----- > From: "richard terry" <rterry@pacific.net.au> > To: "Oliveiros d'Azevedo Cristina" <oliveiros.cristina@marktest.pt> > Sent: Wednesday, December 29, 2010 1:58 PM > Subject: Re: [NOVICE] Extracting unique data from tables/views question > > >> On Thursday 30 December 2010 00:44:35 you wrote: >> >> Ok this: >> >> SELECT fk_patient,MAX(observation_date) as observation_date, >> value_numeric >> FROM documents.vwobservations >> WHERE loinc = '4548-4' >> GROUP BY fk_patient, value_numeric >> >> gives me the values, but I end up with nearly 300 records as patient keys >> are >> duplicated. >> >> ?? >> >> Getting pretty tired as its nearly 1am in AU, so might head off to bed, >> I'll >> pick up my email in the morning, thanks for helping me >> >> Regards >> >> richard >> >> >> >>> Can you show me what this query outputs, please? >>> >>> SELECT fk_patient,MAX(observation_date) as observation_date >>> FROM documents.vwobservations >>> WHERE loinc = '4548-4' >>> GROUP BY fk_patient >>> >>> Best, >>> Oliver >>> >>> ----- Original Message ----- >>> From: "richard terry" <rterry@pacific.net.au> >>> To: "Oliveiros d'Azevedo Cristina" <oliveiros.cristina@marktest.pt>; >>> "PostgreSQL - newbie" <pgsql-novice@postgresql.org> >>> Sent: Wednesday, December 29, 2010 1:37 PM >>> Subject: Re: [NOVICE] Extracting unique data from tables/views question >>> >>> > On Thursday 30 December 2010 00:27:47 you wrote: >>> >> SELECT DISTINCT fk_patient, observation_date, value_numeric >>> >> FROM documents.vwobservations a >>> >> NATURAL JOIN >>> >> (SELECT fk_patient,MAX(observation_date) as observation_date >>> >> FROM documents.vwobservations >>> >> WHERE loinc = '4548-4' >>> >> GROUP BY fk_patient) b >>> >> ORDER BY fk_patient >>> > >>> > Runs ok, but gives weird results all the same patient key, and not >>> > sure >>> > which >>> > ones are hba1c. >>> > >>> > ?? >>> > >>> > Regards >>> > >>> > richard >>> > >>> > 52;"2010-07-02";0.01 >>> > 52;"2010-07-02";0.08 >>> > 52;"2010-07-02";0.43 >>> > 52;"2010-07-02";0.49 >>> > 52;"2010-07-02";1 >>> > 52;"2010-07-02";2.09 >>> > 52;"2010-07-02";2.6 >>> > 52;"2010-07-02";2.8 >>> > 52;"2010-07-02";3.4 >>> > 52;"2010-07-02";3.7 >>> > 52;"2010-07-02";3.89 >>> > 52;"2010-07-02";4.3 >>> > 52;"2010-07-02";4.5 >>> > 52;"2010-07-02";5 >>> > 52;"2010-07-02";6.3 >>> > 52;"2010-07-02";6.6 >>> > 52;"2010-07-02";8 >>> > 52;"2010-07-02";8.9 >>> > 52;"2010-07-02";13.5 >>> > 52;"2010-07-02";14.2 >>> > 52;"2010-07-02";23 >>> > 52;"2010-07-02";25 >>> > 52;"2010-07-02";30 >>> > 52;"2010-07-02";30.5 >>> > 52;"2010-07-02";34 >>> > 52;"2010-07-02";38.2 >>> > 52;"2010-07-02";45 >>> > 52;"2010-07-02";48 >>> > 52;"2010-07-02";60 >>> > 52;"2010-07-02";68 >>> > 52;"2010-07-02";85 >>> > 52;"2010-07-02";97 >>> > 52;"2010-07-02";100 >>> > 52;"2010-07-02";104 >>> > 52;"2010-07-02";136 >>> > 52;"2010-07-02";138 >>> > 52;"2010-07-02";200 >>> > 52;"2010-07-02";202 >>> > 52;"2010-07-02";212 >>> > 52;"2010-07-02";316 >>> > 52;"2010-07-02"; >>> > 53;"2010-07-13";2.2 >>> > 53;"2010-07-13";6.6 >>> > 53;"2010-07-13";26 >>> > > > -- > Sent via pgsql-novice mailing list (pgsql-novice@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/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
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
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;
On Thursday 30 December 2010 14:08:58 Jasen Betts wrote: Jasen, See comments below, thanks heaps. > 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; Yes, thank, that seems to do the tick, and only a unique and the latest observation is returned. Have to head out now, but I'll play around with this tonight. Thanks to everyone who has contributed to this thread, and to any other observations, suggestions made/or coming. Regards Richard