# Re: Limits

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

Message-ID: <ee6e37aa-82a6-4a89-af28-37bcaba5b950_at_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...).

*>
*

> 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.
*

Regards, Jim.

*>
**> Roy
*

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