Chris, there are other ways to do this, but they are version dependant.
Which version of Oracle are you using?
Jared
On Monday 07 May 2001 14:07, Christophe Schockaert wrote:
> Hi all,
>
> I need to get the top N values from a table.
> It's quite easy to formulate in english, but rather interesting to write in
> SQL.
> I found a solution at http://www.4guysfromrolla.com/webtech/110498-1.shtml
>
> The example has a table ItemCost containing ItemNumberID (int) and Cost
> (money).
> The query is:
>
> SELECT rank, ItemNumberID, Cost
> FROM (SELECT T1.ItemNumberID, T1.Cost,
> (SELECT COUNT(DISTINCT T2.Cost) FROM ItemCost T2
> WHERE T1.Cost <= T2.Cost) AS rank
> FROM ItemCost T1) AS X
> WHERE rank<N ORDER BY rank
>
> The problem is that Oracle refuses the (select COUNT ...) in the second
> SELECT clause.
> It seems that I have to use CAST but I'm getting lost with the syntax.
> According to the documentation, I have to use CAST(MULTISET ...) if the
> query will result in several rows. It is also said that scalar subqueries
> as argument of the CAST operator are not valid in Oracle8. Do I have to
> consider SELECT COUNT as a scalar subquery ? It is not a multi-rows query
> anyway.
> However, whether I use CAST, CAST(MULTISET) or just the example above, I
> get an error from Oracle.
>
> Does anybody know how I can translate the example to Oracle, or how I can
> write a query in the Oracle SQL syntax which will give me the result I want
> ?
>
>
> Thanks in advance,
>
> Christophe
>
> >>>>---------------> mailto:[EMAIL PROTECTED]
>
> Once it's perfectly aimed, the flying arrow goes straight to its target.
> Thus, don't worry when things go right.
> There will be enough time to worry about if they go wrong.
> Then, it's time to fire a new arrow towards another direction.
> Don't sink. Adapt yourself ! The archer has to shoot accurately and
> quickly.
> [Words of Erenthar, the bowman ranger] <---------------<<<<
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Jared Still
INET: [EMAIL PROTECTED]
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
