Welcome to Interactive Programming Forums, where you can ask questions, receive answers from other members of the community, post code snippets and post exercies.

Shouldn't "limit" always be preceded by an "order by"?

+8 votes
OK, I know you're using SQLLite, and my experience is with PostgreSQL, but it is my understanding that a limit, and particularly a limit with an offset should be preceded by an order by because the SQL specification does not require that records be returned from a database in any particular order.
(based on the SQL II exercises).  Or has the SQL specification changed recently (my knowledge may be dated)?
posted 12 years ago in SQL by david.bandel (2,060 points)

1 Answer

+2 votes
From the standpoint of a useful query response, LIMIT will typically follow an ORDER BY clause, as you are correct that the SQL Standard does not require any sort of sort order, unless explicitly requested.

However, there are lots of cases, especially among ad hoc queries, where you might want to use LIMIT without ORDER BY.  For example, when examining an unfamiliar database, I will often do "SELECT * FROM FooTable LIMIT 10;" to get a feel for what's in the table.  I made heavy use of this a few days ago when trying to sort out the tables in an unfamiliar (and undocumented) database.

There's definitely no technical reason that an ORDER BY statement should be required for LIMIT.
answered 12 years ago by christopher.cashell (3,820 points)

Related questions

+4 votes
1 answer
posted 11 years ago in XML by yende (9,130 points)
0 votes
0 answers
posted 4 years ago in SQL by gladstonecm (1,210 points)
+1 vote
1 answer
posted 11 years ago in SQL by yende (9,130 points)
+7 votes
1 answer