Garrett Fitzgerald wrote: > I just wrote a query against our EMR database to give me the first and > last of a series of observations. I came up with the following, which > appears to work, but I was wondering if anyone had any thoughts on how > to do it more efficiently. I don't like those correlated subqueries > just to get a max and a min, but I don't know that there's a better > way. > > Thanks! > > SELECT person.lastname, person.firstname, obshead.name, obs.obsdate, > obs.obsvalue, obs2.obsdate, obs2.obsvalue > FROM ml.Person Person > JOIN ml.rptObs Obs ON Person.pID = Obs.pID > JOIN ml.ObsHead ObsHead ON Obs.hdID = ObsHead.hdID > JOIN ml.rptObs Obs2 ON Obs.pID = Obs2.pID > AND Obs.hdID = Obs2.hdID > AND Obs.obsDate < obs2.obsDate > WHERE ObsHead.name LIKE 'SF-12%' > AND Obs.obsDate = (SELECT MIN(obsDate) > FROM ml.RptObs Obs3 > WHERE Obs3.pID = obs.pID > AND Obs3.hdID = obs.hdID) > AND Obs2.obsDate = (SELECT MAX(obsDate) > FROM ml.RptObs Obs4 > WHERE Obs4.pID = obs2.pID > AND Obs4.hdID = obs2.hdID) > ORDER BY Person.lastname, Person.firstname, Obs.obsdate, obshead.name
What is the optimization plan showing? (...Using EXPLAIN or whatever the equivalent is in PL/SQL. It's been a long time for me...late 2004/early 2005 for that. Liked it better than T-SQL though!) The one suggestion I'd make is to possibly create some correlated subqueries that you can JOIN to perhaps. Just a WAG. _______________________________________________ Post Messages to: ProFox@leafe.com Subscription Maintenance: http://leafe.com/mailman/listinfo/profox OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech Searchable Archive: http://leafe.com/archives/search/profox This message: http://leafe.com/archives/byMID/profox/[EMAIL PROTECTED] ** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.
