Subject [firebird-support] Find grid page containing record
Author Tim Ward
Given that a query needed to return data for a page of a grid is of the form

SELECT FIRST 25 SKIP <some multiple of 25>
<some table>.ID (and some other fields of human-readable data)
FROM < <some table> plus tables as needed for other fields in the
WHERE <something, may be complex expressions involving several fields>
ORDER BY <something else, may be several fields>

how can I find out what <some multiple of 25> is for a given <some

(Without using any features that are only in Firebird 3, which are
needed for the solutions I've found so far. Surely this is not a rare
thing to want to be able to do?)

Scenario: Data is displayed in a grid in the user interface, with 25
records per page. The user gets to specify the filters (WHERE) and
sorting (ORDER BY) in the user interface, ie these change outside my
control (and in consequence appropriate tables get pulled into the FROM
clause as necessary by the query generation code).

The wanted operation is that the user can say "show me the record with
ID such-and-such" (by doing something in some other part of the UI), and
the grid will display the correct page of data (and then scroll as
necessary and highlight the wanted record, which obviously we'll have to
do in the UI). In, that is, a sane amount of time - fetching hundreds of
pages of data to the grid sequentially until the right record appears is
not a reasonable solution! ID may or may not be the primary key, but we
can use an indexed unique field if that helps.

Tim Ward