Re: Limits

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

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.

Roy Received on Fri Jul 25 2008 - 11:19:11 CEST