# Re: Limits

Date: Fri, 25 Jul 2008 07:30:01 -0700 (PDT)

Message-ID: <ee6e37aa-82a6-4a89-af28-37bcaba5b950@i20g2000prf.googlegroups.com>

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 - 09:30:01 CDT