Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: top N or rewriting MAX

Re: top N or rewriting MAX

From: Roger Wernersson <roger.wernersson_at_adra.se>
Date: 1997/01/13
Message-ID: <32DA094D.1D38@adra.se>#1/1

Roger Wernersson wrote:
> otierney_at_freenet.edmonton.ab.ca wrote:
> > Does anyone know of good (ie FAST) ways of getting the top
> > N rows in a table?
> This is actually quite simple. This example assumes you have a table
> MY_TABLE with a numeric column VALUE. The SELECT statement will give you
> the two lowest values.

You stupid! You are dead wrong! It is much simpler to create a view with a GROUP BY clause and then use ROWNUM to get the lowest values, though you might be in for some trouble if you want the highest ones.

CREATE VIEW test_view AS
SELECT value
FROM test
GROUP BY value;

SELECT value
FROM test
WHERE ROWNUM < 10;

-- 
Sport radio: people listening to people watching people having fun
Mailto:roger.wernersson_at_adra.se
BTW: All opinions are mine, all mine, and nobody's but mine.
Received on Mon Jan 13 1997 - 00:00:00 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US