Daily Archives: March 25, 2011

Limit and Order by

Remember to make sure your ordering is precise when doing a limit or whatever your SQL dialect wants you to do. 

Since I normally only screw this up at the start of a project this was the second time I’ve done it but it’s an odd one.  You’d kind of think that the fundamental nature of the limit clause would mean you’d need a consistent order for it to be useful.  If you do a query that asks for the first 10 records and then another that’s identical but asks for the next 10 you probably assume that’s what you’d get.  You have to remember this is SQL that the results returned are un-ordered unless specified.  If your order by is not specific enough you can get a random set of results each time.  Typically the same exact same query, even un-ordered returns the same results, but if you’re changing the limit clause it’s not an identical query and so the query plan may be subtly different and so you can’t be sure which records you’ll get.  In fact your order by needs to specify the ordering predictably for every single row to get consistent results.  An order by on a field that’s not distinct will still allow some degree of unpredictability and probably defeat the point of your limit on your query.

Tagged
Follow

Get every new post delivered to your Inbox.

Join 64 other followers