SQL sort with limit? (non trivial)

5
votes
table {
  id: long
  name: string
}

1235 Fred
1902 Trever
5123 George
6467 Derek
7868 Joe
8972 Bob
9272 Alf
9842 Hank

I want to return 2 records prior to that of Joes, in ascending order.

i.e The correct values should be:

5123 George
6467 Derek

Any thoughts? FYI:

  1. Returns incorrect rows:

    select * from table with id<7868 order by id asc limit 2

  2. Returns incorrect sort order:

    select * from table with id<7868 order by id desc limit 2

3
javasql
The database used is SQLite - but dont let this stop you answering with respect to other RDBMS - it would be nice to know anyway :D - Matt

3 Answers

5
votes
SELECT * FROM
  (select * from table where id<7868 order by id desc limit 2) AS foo
ORDER BY ID ASC
1
votes

Try:

Select * from (
    select * from table with id<7868 
    order by id desc limit 2
) as t order by id asc

Doing the subquery lets you first get the correct rows, then you can re-order them afterwards

0
votes

In PostgreSQL:

select * from "table" where id < 7868 order by id asc limit 2 offset 2

And similarly in MySQL (I believe) "limit 2, 2"

"LIMIT 2 OFFSET 2" works in SQLite too, at least with the version I tried (3.6.13)