Daniel_L Daniel_L - 2 months ago 10
SQL Question

sqlite order by after limit offset

So I can paginate a query with

LIMIT
and
OFFSET


SELECT *
FROM db.table
LIMIT 6
OFFSET 0


But if I try to
ORDER BY
this result I get the first
n
ordered rows, not the first
n
rows ordered.

SELECT *
FROM db.table
LIMIT 6
OFFSET 0
ORDER BY first_name COLLATE NOCASE


So instead of

bob
charles
dave
dan
doug
durrand


I want to get

bob
doug
elliot
manny
ralph
xavier

Answer

You want a subquery:

SELECT t.*
FROM (SELECT * 
      FROM db.table
      LIMIT 6 OFFSET 0
     ) t
ORDER BY first_name COLLATE NOCASE;

Note that this actually returns an indeterminate set of rows, which is then ordered.

Unless you use order by, then you are not necessarily guaranteed that the same set of rows is returned.