Re: top N or rewriting MAX

Roger Wernersson wrote:
>
> 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;

Before you say other people stupid, you should be sure your answer is correct.

I do not think your answer will work.

Try this:

    SELECT Column_1

       FROM Table_1 A
       WHERE  10 < (SELECT COUNT(*)
			FROM Table_1 B
			WHERE A.Column > B.Column);

to select top 10 rows.

If 'column_1' is not unique, you should adjust the statement with '>=' and '<='.

-- 
Hui, Kwok Miu (Xu, Guo Miao)         E-Mail: huikomix_at_dt.com.hk
Assistant MIS Manager                Tel   : (00852)2589 3369
Cho Yang (Hong Kong) Co., Ltd.       Fax   : (00852)2559 3049