Re: Limits

On Jul 25, 10:19 am, Roy Hann <specia..._at_processed.almost.meat> wrote:
> JOG wrote:
> > Does anyone else find LIMIT statements particularly irritating? I
> > mean, as far as I know it has no basis in relational algebra, and yet
> > I find myself using it constantly. (In Oracle 11g I am still forced to
> > use the hideous ROWNUM which is even more irritating, and yet there I
> > appear to be, row-numming away till the cows come home).
>
> > So my question to cdt. Can LIMIT type operations, given their apparant
> > utility, be framed in terms of relational algebra, and if so how
> > elegantly can this be done?
>
> The queries to do that kind of thing can be written, but they are
> not very intuitive. I present them when I am teaching an SQL course
> because if you can understand how they work then you really understand
> SQL grouping and aggregation well. Consider this query that is
> notionally intended to find the three most expensive parts:
>
> SELECT * FROM parts p1
> WHERE (SELECT COUNT(DISTINCT p2.unit_cost)
> FROM parts p2
> WHERE p2.unit_cost > p1.unit_cost) < 3

Yes, that's effectively taking the same counting and grouping approach, written in a more compact way (although here you just have the top 3 and not a range, which would convlute the query somewhat...).

However, what really jars me in your query though is its implict casting of a (nested) relation into an integer as part of the WHERE clause.

>
> First of all, having taught many hundreds of very bright graduates
> working for the big consultancies, I can literally count on the fingers
> of one hand the number who ever claimed to "get" how this works.
>
> Second--and this is the important bit IMO--this quuery doesn't even do
> what the question asks for, and the reason is that the question itself
> is broken. The question presumes there is just one set of three parts
> that are the most expensive. But clearly we could have gazillions of
> parts with an identical price. The query above returns them all,
> thankfully.
>
> The point is that LIMIT and FIRST and all those types of limiters allow
> you to proceed in blissful ignorance that you are asking a stupid
> question stupidly.

As it is with ORDER BY I guess, which is supposed to spit out a linear ordering where only a partial ordering may exist.

Regards, Jim.

>
> Roy
Received on Fri Jul 25 2008 - 16:30:01 CEST