Re: Tricky query

On Thu, 31 May 2001 01:35:57 GMT, willjamu_at_mindspring.com (James Williams) wrote:

>On Wed, 30 May 2001 23:49:53 +0800, Dino Hsu <dino1_at_ms1.hinet.net>
>wrote:
>
>Oracle 8i has a ranking function as I was shown this morning. Prior to
>Oracle 8 I would think some complex PROC or PL/SQL was required.
>

Yes, I checked the 8.1.7 documentation (SQL Reference), a new type of functions called 'analytical functions' are invented, in addition to 'user functions' and 'aggregate functions'. These analytical functions are particularly useful in OLAP or Data Warehousing applications. Before this, what we could do is called 'psudocolumns' such as rownum, but it lacks the syntax 'OVER', 'PARTITION BY', 'ORDER BY' as found in analytical functions. Moreover, many new functions are introduced, including RANK(), RATIO_TO_REPORT(), PERCENT_RANK(), etc.. It seems there are some useful improvements from 8 to 8i. I cannot read through the whole SQL Reference at the moment, but I believe there still are more interesting things to be found in 8i.

Dino Received on Thu May 31 2001 - 06:24:52 CDT