How does SQL LIMIT ordering work

Question

If you select from a sql database and add a limit, and run it multiple times, the results will vary. However, when you want to select randomly from a database you usually do:

order by random()
limit n

My question is how does sql limit ordering work. If its not random then how are the results being varried?

in regards to the downvotes, would appreciate feedback as to how to improve the question. — mt88, Jul 17 '15 at 22:00
Hi, I would recommend doing [a search](http://stackoverflow.com/search?q=sql+limit+random). — Kermit, Jul 18 '15 at 02:26

score 3 · Answer 1 · answered Jul 17 '15 at 18:41

LIMIT doesn't do ordering, period. If you want a specific ordering, use ORDER BY.

If no ORDER BY is specified, then the rows are not returned in any specific order. They may be the same from run to run, but it depends on all sorts of things having to do with retrieval of rows from the underlying data file, or indexes, or join order. Regardless of what might control ordering when no ORDER BY is specified, you should not depend on it.

score 1 · Accepted Answer · answered Jul 17 '15 at 18:41

It varies by each vendor's implementation, but generally the entire result set is generated, then the ORDER BY is applied if it exists, and then the server only returns n rows to the client.

Note that LIMIT or TOP without ORDER BY is non-deterministic. That is, the results are not guaranteed to be consistent between executions.

How does SQL LIMIT ordering work

2 Answers2