Find row number in a sort based on row id, then find its neighbours

Question

Say that I have some SELECT statement:

SELECT id, name FROM people
   ORDER BY name ASC;

I have a few million rows in the people table and the ORDER BY clause can be much more complex than what I have shown here (possibly operating on a dozen columns).

I retrieve only a small subset of the rows (say rows 1..11) in order to display them in the UI. Now, I would like to solve following problems:

1. Find the number of a row with a given id.
2. Display the 5 items before and the 5 items after a row with a given id.

Problem 2 is easy to solve once I have solved problem 1, as I can then use something like this if I know that the item I was looking for has row number 1000 in the sorted result set (this is the Firebird SQL dialect):

SELECT id, name FROM people
   ORDER BY name ASC
   ROWS 995 TO 1005;

I also know that I can find the rank of a row by counting all of the rows which come before the one I am looking for, but this can lead to very long WHERE clauses with tons of OR and AND in the condition. And I have to do this repeatedly. With my test data, this takes hundreds of milliseconds, even when using properly indexed columns, which is way too slow.

Is there some means of achieving this by using some SQL:2003 features (such as row_number supported in Firebird 3.0)? I am by no way an SQL guru and I need some pointers here. Could I create a cached view where the result would include a rank/dense rank/row index?

Answer 1

Firebird appears to support window functions (called analytic functions in Oracle). So you can do the following:

To find the "row" number of a a row with a given id:

select id, row_number() over (partition by NULL order by name, id)
from t
where id = <id>

This assumes the id's are unique.

To solve the second problem:

select t.*
from (select id, row_number() over (partition by NULL order by name, id) as rownum
      from t
     ) t join 
     (select id, row_number() over (partition by NULL order by name, id) as rownum
      from t
      where id = <id>
     ) tid
     on t.rownum between tid.rownum - 5 and tid.rownum + 5

I might suggest something else, though, if you can modify the table structure. Most databases offer the ability to add an auto-increment column when a row is inserted. If your records are never deleted, this can server as your counter, simplifying your queries.