sqlite order by after limit offset

Question

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

score 2 · Accepted Answer · answered Sep 08 '16 at 23:47

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.

score 2 · Answer 2 · answered Sep 08 '16 at 23:53

"First n rows" doesn't make sense in this context. Tables don't have an intrinsic order; you have to specify it. Usually this is done with some ID column that increments automatically to track the order of insertion (which is probably what you mean).

You're specifying the order in your ORDER BY clause, which is why it's ordering that way. If you have an ID column, however, you can do a subselect like:

select name from (
    select name from foo
    order by id
    limit...
) order by name

sqlite order by after limit offset

2 Answers2