SQL Problem. HELP!

[Quoted] [Quoted] I am lookin for a way to solve a problem. I need to find the highest values in a list and only return those values (i.e., find the three highest values and only return those three rows)

I found this "solution" on the INQUIRY.COM web page, but it does not work within Oracle:

        CREATE TABLE SalesReport (name CHAR(20) NOT NULL,
                          territory INTEGER NOT NULL,
                          amount DECIMAL (8,2) NOT NULL);

        The following query gives the highest SalesReport the rank of
1,        the
next highest the rank of 2 and so forth. The query can  handle ties
within a rank. If the territory has less than (n)       members, then it

               SELECT S0.territory, S0.name, S0.amount,
                  (SELECT COUNT(DISTINCT amount)
                FROM SalesReport AS S1
                  WHERE (S1.amount >= S0.amount)
                AND (S1.territory = S0.territory)) AS rank
                   FROM SalesReport AS S0
                 WHERE rank <= 3; -- adjustable parameter

Here's what happens when I run the query:

        SQL> run
         1  SELECT S0.territory, S0.name, S0.amount,
         2                  ((SELECT COUNT(DISTINCT amount)
         3                       FROM SalesReport AS S1)
         4                       WHERE (S1.amount >= S0.amount)
         5                  AND (S1.territory = S0.territory))) AS rank
         6                    FROM SalesReport AS S0
         7*                      WHERE rank <= 3
                ((SELECT COUNT(DISTINCT amount)
                  *
        ERROR at line 2: 
        ORA-00936: missing expression 

What's wrong with this query.

Any help would be appreciated!

Thanks.

Jerry Glass Received on Mon Jun 16 1997 - 00:00:00 CEST