You need to select the maximum value of the primary key (id).

I make a request:

select id from peoples order by id desc rows 1;

I get the query plan: PLAN SORT ((PEOPLES NATURAL))

same query but sorting direct:

 select id from peoples order by id rows 1;

I get the query plan: PLAN (PEOPLES ORDER RDB$PRIMARY43)

How to force Firebird 2.5 to use an index when reverse sorting?

PS: if you create an additional index by id , then when you query this newly created index is used.

    1 answer 1

    Probably no additional index in any way. From the documentation:

    Compared to other DBMS, indexes in Firebird have one more feature - the index scan is always unidirectional, from smaller keys to big ones. Often, because of this, the index is called unidirectional and it is said that in its node there are only pointers to the next node and there is no pointer to the previous one. ...

    This feature leads to the impossibility of using the ASC-index for DESC-sorting or calculating MAX and vice versa, the impossibility of using the DESC-index for ASC-sorting or calculating MIN.

    • Thank. This is bad. - Vladimir