Re: how to find "bottom" of table in a group query

Matthias,
I think I've managed to find the article you refer to, but the man has posted so much to the newsgroup I might not have the best copy.

However, although it is very cunning, it has been made unsafe by changes in Oracle.

The sample I found in dejanews

        select /*+ rule */ emp.* from emp, dual
        where -1 * emp.sql = decode(dual.dummy(+),'X',null,null)
        and rownum <= 1000
        order by emp.sal desc

    This works because the rule based optimiser will do a     sort merge join, (sorting by -1*sal, hence getting descending     order).

    However - if you do not use the hint, then the cost     based optimiser could choose to do a hash join     and give you the wrong results.

    Moreover, if you include the RULE hint but the EMP     table is a parallel table the cost based optimiser will     cut in anyway (post 7.3-ish), and you could still get a hash join.

    Upshot - I love it, but it's been superseded by changes to Oracle

--
Jonathan Lewis
Yet another Oracle-related web site: www.jlcomp.demon.co.uk

I have a spelling chequer
It came with my pea sea
It plainly marques for my revue
Miss steaks eye cannot sea

I've run this poem threw it
I'm shore your pleased to no
Its letter perfect in it's weigh
My chequer tolled me so

Matthias Gresz wrote in message <36DE31C0.AC0C783B_at_t-online.de>...
>
>>
>> As others have pointed out,though, there is no
>> 'nice' solution to general 'bottom 10'/'top 10' problem.
>>
>
>I disagree:
>check out dejanews archive for Jurij Modics TOP X solution posted here
>last year!
>
Received on Sat Mar 06 1999 - 08:47:46 CST