Sort / limit order on simple db query

Question

I'm wondering about how a query like the following will work....

select * from survey where Year < '2000' order by Year desc limit 1

My question is, does the limit apply:

1. After the records are sorted, in which case I'll definitely get the last record before the year 2000. or...

2. Before the records are sorted, in which case a single record will be picked from natural order & then not sorted at all (since there's only one record).

This seems somewhat obvious but Amazons documentation doesn't seem to cover it.

Answer 1

This seems somewhat obvious but Amazons documentation doesn't seem to cover it.

Yo, still the answer is definitely 1), otherwise the limit clause wouldn't be useful for any real world scenarios at all (see below); in addition it would severely violate the Principle of least suprise, insofar the Amazon SimpleDB syntax is modeled after SQL, which works as expected in this regard, see e.g. the LIMIT clause in PostgreSQL:

When using LIMIT, it is a good idea to use an ORDER BY clause that constrains the result rows into a unique order. Otherwise you will get an unpredictable subset of the query's rows — [...].

For example, otherwise SimpleDB wouldn't allow proper pagination, which is orchestrated via limit indeed (see Mocky's answer for details on How to do paging with simpledb?), i.e. you'll receive a next token in case there are more results, which wouldn't yield correctly sorted pages, if limitwouldn't obey order by.

Accordingly, to finally provide some reasoning deduced from Amazon's documentation, Count hints towards the expected behavior as well:

The next token returned by count(*) and select are interchangeable as long as the where and order by clauses match.