CODEWITHSUNDEEP
sqlsqlite
nabulke
nabulke

Reputation: 11275

Using limit in sqlite SQL statement in combination with order by clause

Will the following two SQL statements always produce the same result set?

 1. SELECT * FROM MyTable where Status='0' order by StartTime asc limit 10

 2. SELECT * FROM (SELECT * FROM MyTable where Status='0' order by StartTime asc) limit 10

Upvotes: 8

Views: 13261

Answers (2)

GarethD
GarethD

Reputation: 69769

Yes, but ordering subqueries is probably a bad habit to get into. You could feasibly add a further ORDER BY outside the subquery in your second example, e.g.

SELECT * 
FROM (SELECT * 
      FROM Test
      ORDER BY ID ASC
     ) AS A
ORDER BY ID DESC
LIMIT 10;

SQLite still performs the ORDER BY on the inner query, before sorting them again in the outer query. A needless waste of resources.

I've done an SQL Fiddle to demonstrate so you can view the execution plans for each.

Upvotes: 8

ypercubeᵀᴹ
ypercubeᵀᴹ

Reputation: 115550

No. First because the StartTime column may not have UNIQUE constraint. So, even the first query may not always produce the same result - with itself!

Second, even if there are never two rows with same StartTime, the answer is still negative.

The first statement will always order on StartTime and produce the first 10 rows. The second query may produce the same result set but only with a primitive optimizer that doesn't understand that the ORDER BY in the subquery is redundant. And only if the execution plan includes this ordering phase.

The SQLite query optimizer may (at the moment) not be very bright and do just that (no idea really, we'll have to check the source code of SQLite*). So, it may appear that the two queries are producing identical results all the time. Still, it's not a good idea to count on it. You never know what changes will be made in a future version of SQLite.

I think it's not good practice to use LIMIT without ORDER BY, in any DBMS. It may work now, but you never know how long these queries will be used by the application. And you may not be around when SQLite is upgraded or the DBMS is changed.

(*) @Gareth's link provides the execution plan which suggests that current SQLite code is dumb enough to execute the redundant ordering.

Upvotes: 7

Related Questions