Re: Limits
From: Bob Badour <bbadour_at_pei.sympatico.ca>
Date: Fri, 25 Jul 2008 13:10:02 -0300
Message-ID: <4889fada$0$4042$9a566e8b_at_news.aliant.net>
>
> 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
Date: Fri, 25 Jul 2008 13:10:02 -0300
Message-ID: <4889fada$0$4042$9a566e8b_at_news.aliant.net>
Roy Hann 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
>
> 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
It's actuall wrong for more reasons than that. If two competitors tie for a gold medal, we don't give the next guy a silver mdeal. Received on Fri Jul 25 2008 - 18:10:02 CEST