> I suggest the reason LIMIT hasn't been standardized is that it's contrary to > the fundamental idea that rows in a table have no meaningful order. SQL > doesn't honor relational theory with complete > fidelity, but at least that horse is still in the barn.
Point 1: I think you'll find plenty of bits of SQL that are not relational -- that's not the reason. Point 2: LIMIT is perfectly relational, as long as it is applied with ORDER BY. While the data set has no order, that in no way prevents performing a query that does. Such as: "Show me the customers that are within the first 10 when ordered by name." You can't sort the relation, but you can certainly apply an order when performing a query. How else would MIN() work? > The problem with LIMIT is it's not based in the data. Cutting off results at > some arbitrary N tells you *nothing* about the data other than that N or more > rows met the criteria. Note that predicate logic has constructs for "for > all" and "there exists" , but not "are some"! > > I have yet to see a query using LIMIT 1 posted on this list that cannot be > expressed -- better, IMO -- with min(). Queries that limit the results to > "top N" to support things like pagination inevitably include assumptions > about transactionality (or lack thereof) that are either invalid or ill- > considered. Every one would be better served either by just fetching the > needed rows as required (and letting pending rows pend), or by supplying the > last "high" value as a minimum for the WHERE clause instead of an OFFSET. > Were I a fan of conspiracies, I'd suspect the LIMIT-OFFSET constructs were > invented by antilogicians to prevent learning and hobble performance. Your criticism re LIMIT is mistaken. It is a perfectly reasonably way to support pagination, and can actually be performed (rather laboriously) using SQL Window functions (which Sqlite does not have). It can also be useful in some algorithms where you need the top 2 or 3 or whatever. Your criticism re OFFSET has some basis. It is usually better (as you suggest) to provide a previous row value for pagination. It's just that once you've done LIMIT, OFFSET is easy to implement and sometimes useful. > By the way, i'm also a LIMIT club member, with limits. I use it for > convenience on the command line while exploring data. It's great for that, > in the absence of pager support. Maybe keeping it a little "weird" will help > remind new developers to use it as a convenience instead of a crutch. I think most developers are just astonished at how much it differs from one dialect to another. Astonished, but not impressed. Regards David M Bennett FACS Andl - A New Database Language - andl.org
