Re: select x max values

From: André Klück <akluck01_at_wxs.nl>
Date: Thu, 24 Aug 2000 20:54:39 +0200
Message-ID: <39A56F6F.1737C406@wxs.nl>

Miran,

Your actual query (in the from-clause) might be a simple one, but this certainly works in ALL cases with little overhaed.

Select the n-highest/lowest results of a (complicated) query.

Combinations of joins, hints, GROUP BY and ORDER BY might give you a long list of results, while
you are only interested in part of the resultset. ROWNUM and ORDER BY do not go together to fix this problem.

This query can give you the right set without performance lost:

SELECT A.column1 col1

     , A.column2 col2
     , ROWNUM    ranking
FROM (SELECT SUM(columnX) column1
           , SUM(columnY) column2
           , columnZ      column3
     FROM test
     GROUP BY columnZ            ) A
   , SYS.DUAL                      B

WHERE -1*A.column2 = DECODE(B.dummy(+),'X',0,0)   AND ROWNUM <= n
;

Check out the executionplan of this statement: First tables A and B will be used (B with a full scan, but that's ok).

After access of table A, the results are joined with those of B (merge join).

In order to do this properly Oracle will first SORT the results of A and B, than the outer join will follow. The sorting on B is what we are interested in.
Default Oracle will sort ASCENDING. First on A.column2, because it is in the WHERE-clause.
That column will be merged with the DECODE of B.dummy.

Because values from table B are all the same, the SORT is in effect on tha values from table A.
This is what we want. Mutiply by +1 for ASC, by -1 for DESC (check this yourself).

After the SORT we want to restrict on the amount of rows by ROWNUM. Joining with DUAL will give a small overhead on performance.

sUCCes,
André

Miran wrote:

> My problem:
> I vant to select x max values from a table .
> For example
> My table is prices and it's values ar 10,11,12,13,14,15
> I want to select 3 max values form this table, and I dont want it to be
> any function or procedure just select. I have a solution but it's preate
> slow, so if you have any idea ...
>
> miran
Received on Thu Aug 24 2000 - 13:54:39 CDT