Re: Is relational theory irrelevant?

From: Mikito Harakiri <mikharakiri_at_iahu.com>
Date: Fri, 14 Nov 2003 14:14:03 -0800
Message-ID: <oCctb.23$6v2.33@news.oracle.com>

"Christopher Browne" <cbbrowne_at_acm.org> wrote in message news:bp3ir9$1k22pm$3_at_ID-125932.news.uni-berlin.de...
> I would have thought that taking the various
> "LIMIT" and "OFFSET" "TOP" keywords and standardizing it would be more
> sensible.

I suggest that all those goofy keywords are redundand if we have RANK():

select * from (

   select ename , sal, rank() over (order by sal desc) as rnk from emp

) where rnk <=5

> The rank functions look to me as though they have the potential to get
> _really_ expensive to evaluate, and not because they are buying some
> massive advantage.
>
> Fabian Pascal presents, as a "standard" way of doing it,
> SELECT a.ename, a.sale_amt
> FROM sales a, sales b
> WHERE a.sale_amt <= b.sale_amt
> GROUP BY a.ename, a.sale_amt
> HAVING COUNT(*) <= q
> ORDER BY a.sale_amt

In Date's article credit goes to Adrian Larner.

Besides, there seems to be an error here:

> WHERE a.sale_amt <= b.sale_amt

------------------^^^^

a.sale_amt < b.sale_amt

> This query is _clever_; it is anything but straightforward, and I'd
> expect horrendous performance.

Unless it is rewritten by optimizer. Received on Fri Nov 14 2003 - 16:14:03 CST