Always remember to check the explain plan! :)

The first one should work. There appears to be a little bug in Oracle where it goes the wrong way with ROWNUM queries sometimes. It pushes the ROWNUM restriction into the subquery which, of course, yields the wrong results. The way to get around this is to abstract it yet another level, as you have. Sometimes adding an extra SELECT value 'x' will do it. This forces it to treat the inner SELECT as a view and sometimes changes the plan. You should also be able to hint it. I forget which, maybe the PUSH_SUBQ might do the trick. Either that or NO_MERGE or something. Sorry about that - it usually works. Oracle just gets confused sometimes. You would think that upon noticing a ROWNUM being used, it would know not to mess with the sub-query structuring of the statement. Oh well

As for the sorting overhead issue - It is smaller than going into the table twice. Besides, you had to do some sorting to get the max() all those times. Check the plans and time the satements to make sure, but as long as you are not severely limited on sort area, you should find that my approach is faster.

Hope this helps,

- Chris

- Chris