On Fri, Oct 10, 2008 at 12:56 PM, MB Software Solutions General Account <[EMAIL PROTECTED]> wrote: > 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. -------------------------
i was going to say the same thing there Michael. Make the min and max value tables with the key to join back in temp INDEXED tables and join them in. -- Stephen Russell Sr. Production Systems Programmer Mimeo.com Memphis TN 901.246-0159 _______________________________________________ Post Messages to: [email protected] 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.
