SQL sort with limit? (non trivial)

Question

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

Answer 1

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

Answer 2

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

Answer 3

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)