Re: Finding the next best answer

> This is the same as: > SELECT tab1.name, tab1.obj1, MIN(link.pri) > FROM tab1 INNER JOIN link > ON tab1.obj1 = link.obj1 > GROUP BY tab1.name, tab1.obj1; > which is, to my mind, easier to read. You're right of course. In my zeal to simplify the problem I took out the fact there is additional information in the link table, and I want only the values of these fields from the matching row with the minimum priority. But yours is a much better answer to the question I actually asked... > Don't try to do the whole thing with inline-views, use a temporary table: > Assuming that your not constrained to a single statement, of course. Ah, yes. Actually, I am constrained to a single statement; forgot to mention that. Work with the same artificial horizons for too long and you forget they're there... or at least forget they're artificial. I've done a solution modelled on Bozons views but with the views inline; its VERY ugly (not Bozons fault; _I_ put them inline) - and I suspect very inefficient - but it does seem to work. Essentially, since I couldn't get the back reference to the previously calculated "win" value to work, I re-calculate "win" again in the process of caluclating "place", and then calculate "place" a second time in the process of calculating "show" (which, of course, requires calculating "win" yet a third time.) I'm frankly rather disgusted by this, but it probably won't be TOO slow - the link table is indexed on the appropriate field, and should have 4 or 5 priorities tops for each one. Still, if someone comes up with something a bit less of a blunt instrument I'd appreciate hearing about it. Thanks again for your help guys, - rob.

Dev wrote: > Ah, yes. Actually, I am constrained to a single statement; forgot to > mention that. Work with the same artificial horizons for too long and > you forget they're there... or at least forget they're artificial. > > I've done a solution modelled on Bozons views but with the views > inline; its VERY ugly (not Bozons fault; _I_ put them inline) - and I > suspect very inefficient - but it does seem to work. Essentially, > since I couldn't get the back reference to the previously calculated > "win" value to work, I re-calculate "win" again in the process of > caluclating "place", and then calculate "place" a second time in the > process of calculating "show" (which, of course, requires calculating > "win" yet a third time.) I'm frankly rather disgusted by this, but it > probably won't be TOO slow - the link table is indexed on the > appropriate field, and should have 4 or 5 priorities tops for each one. Don't forget that choosing the optimal query plan is NP-Hard. Informix's optimiser does a better done that it often gets credit for, but you've got to give it a fighting chance. Two words: UPDATE STATISTICS. > Still, if someone comes up with something a bit less of a blunt > instrument I'd appreciate hearing about it. Okay, how about on the server you: CREATE ROW TYPE tab1_pris_row ( obj1 INTEGER, name CHAR(20), win INTEGER, place INTEGER, show INTEGER ); CREATE FUNCTION build_tab1_pri() RETURNS MULTISET(tab1_pris_row NOT NULL); DEFINE l_bag MULTISET(tab1_pris_row NOT NULL); DEFINE l_row tab1_pris_row; DEFINE l_obj1 INTEGER; DEFINE l_name CHAR(20); DEFINE l_pri INTEGER; DEFINE l_count INTEGER; DEFINE l_null INTEGER; LET l_null = NULL; FOREACH SELECT obj1, name INTO l_obj1, l_name FROM tab1 LET l_count = 0; LET l_row = ROW(l_obj1, l_name, l_null, l_null, l_null)::tab1_pris_row; FOREACH SELECT FIRST 3 DISTINCT pri INTO l_pri FROM link WHERE obj1 = l_obj1 ORDER BY pri ASC LET l_count = l_count + 1; IF l_count == 1 THEN LET l_row.win = l_pri; END IF IF l_count == 2 THEN LET l_row.place = l_pri; END IF IF l_count == 3 THEN LET l_row.show = l_pri; EXIT FOREACH; END IF END FOREACH IF l_count == 0 -- There are no links for this obj1 at all THEN CONTINUE FOREACH; -- so ignore it END IF INSERT INTO TABLE(l_bag) VALUES(l_row); END FOREACH RETURN l_bag; END FUNCTION; ... then, in your application you can just: SELECT * FROM TABLE(build_tab1_pri()); -- rh

Dev said >VERY ugly (not Bozons fault; _I_ put them inline) Yes, I write very beautiful SQL. :-) Here is the solution, Don't put them inline. Views exist forever (well until dropped) create them once permantly and then you have the one simple statement using the views over and over. I not sure why you want to do them inline. Views were made for this simplification of SQL and IMHO aren't used enough by smart people because they can understand complicated SQL. I say use the brain cells for something else and write simple SQL. Why do people not like views? I am going to start a view fan club, send requests to join to me. I'll send you a T-Shirt for a small membership fee. ;-) >- and I suspect very inefficient - but it does seem to work. Only if you are having to recalculate Win, and Place because of link problems. I am not sure if they are any less effecient than anything else. Even if you sort and take the first 3 you have to sort all of the data to find the first 3. If speed of this is really important you could have an index: create index link_1x on link(obj1, pri desc) ; This should give you the min very fast for each obj. Of course, I don't feel like creating millions of rows to prove this. The other good thing about views is that they will use, certainly, the underlying indexes on a table if they exist where as with temp tables you have to create new indexes. Don't get me wrong I love temp tables too.