Geek asks and answers

Find the row number in the sort based on the row id, then find its neighbors

Let's say that I have an instruction SELECT:

SELECT id, name FROM people
   ORDER BY name ASC;

I have several million rows in a table people, and the sentence ORDER BYcan be a lot more complicated than what I showed here (maybe it works on dozens of columns).

I only retrieve a small subset of lines (e.g. lines 1..11) to display them in the user interface. Now I would like to solve the following problems:

  • Find the line number with the given id.
  • Show 5 elements and 5 elements after the line with the given id.

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

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

I also know that I can find rank lines by counting all the lines that go up to what I'm looking for, but this can lead to very long sentences WHEREwith tons ORand ANDin condition. And I have to do it repeatedly. With my test data, it takes hundreds of milliseconds, even when using properly indexed columns, which is too slow.

- , SQL: 2003 (, row_number, Firebird 3.0)? SQL, . , / /?

+5
1

Firebird, , ( Oracle). , :

id:

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

, .

:

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

- , . . , , .

+3

More articles:


All Articles