SQL Problem! HELP!

From: Jerry Glass <gglass_at_uspto.gov>
Date: 1997/06/16
Message-ID: <33A562C0.6EEC@uspto.gov>#1/1

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 will

return all the members.

	       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 CDT