Re: SQL query needing Oracle cast

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:Christophe.Schockaert_at_spacebel.be
>
> 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: jkstill_at_cybcon.com

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: ListGuru_at_fatcity.com (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).