> I am quite certain nevertheless that LIMIT has no relational basis. > Nothing based on Order By could.
Then you are mistaken. 1. Consider the following set S of integers: 1,3,5,7,42,99,83,11. 2. Divide it into two subsets such that S1 is of size 3 and all members of S1 are larger than those in S2. A sort is unnecessary -- there are many algorithms that can do that purely based on set logic, the ability to compare members and the ability to determine the cardinality of a set. Another query: calculate the average of that set of numbers after excluding the 2 largest and 2 smallest values. Again, a pure set operation. > You lost me at "subset S of N tuples". Which relational operator takes N as > an argument? Restriction determines whether a tuple should be included or not; you also need cardinality and less than (for comparing members). > > Not so. In standard SQL ORDER BY establishes a comparison function > > between tuples and is part of the DECLARE CURSOR syntax, but the > > cursor exists regardless. > > Regarding Order By and cursors, I'm referencing CJ Date. The reason Order By > cannot appear in a subquery is that its output is not a table, but a cursor. > Whether Order By "establishes a comparison function between tuples" is > irrelevant; so too does Where. It's the product that's different. He's correct as far as that goes. But curiously, ORDER BY LIMIT N could appear in a subquery because it merely selects a subset -- the actual ordering is irrelevant. For this query: calculate the average of that set of numbers after excluding the 2 largest and 2 smallest values. Again, a pure set operation. A reasonable solution would be to use two subqueries with ORDER BY ASC/DESC and LIMIT 2, followed by an aggregation. I don't know if any dialect of SQL would allow that, but it's relationally valid. BTW Andl does allow it. > If you accept that Order By can appear only in the outermost query, it's > clear that it has no relational role at all. It affects only the order in > which the rows are returned to the caller. Other than syntax, how is that > different from a cursor? As defined in the SQL standard ORDER BY can only appear that's true, but the standard does not include LIMIT. > So it's just a tiny communication optimization? After all, compared to a > network round trip, sorting the result (in order to apply LIMIT > rationally) is usually far more expensive. I bet no study has ever shown > LIMIT to improve performance measurably, not that that would justify its > existence. [This is a sidetrack but no, in most cases network round-trip is important enough to warrant a solution, although LIMIT is not the only solution.] > Then I think you mean you agree! Because LIMIT is nonrelational, it's > *undefined*. We have a long thread here that might be titled "what should > LIMIT do?" There's no debate about the relational operators. You might be surprised to learn that there is considerable academic uncertainty as to exactly which operators should be included. For example, is CTE RECURSIVE relational, or not? What about LEAD and LAG in the windowing functions? What about string concatenation as an aggregation operator? There's lots more down this particular wormhole, if you want to pursue it. Regards David M Bennett FACS Andl - A New Database Language - andl.org
