I want to return the first x rows, but also need the total number of items in my select. In MSSQL I figured it out, but I didn't find a solution to the the same in Interbase.
Does such statement exist also for Firebird?
And one general question, is such embedded function (also in MSSQL) actually faster than to execute a second statement in order to get the total rowcount?
If you are using Firebird 3, you can use window functions:
select count(*) over(), column1, column2, etc
from sometable
where somecondition
order by something
fetch first 10 rows only
This uses the fetch clause introduced in Firebird 3.
In earlier versions doing this is a bit harder, but for example in Firebird 2.1 and higher, you could use a common table expression to prevent having to repeat the query and conditions:
with actualquery as (
select column1, column2, etc
from sometable
where somecondition
)
select (select count(*) from actualquery), column1, column2, etc
from actualquery
order by something
rows 10
This uses the rows clause (which is similar to select first 10)