firebirdfirebird2.5

In Firebird, how to aggregate the first N rows?

I would like to do something like this:

CNT=2;

//[edit]
select avg(price) from (
  select first :CNT p.Price
  from Price p
  order by p.Date desc
);

This does not work, Firebird does not allow :cnt as a parameter to FIRST. I need to average the first CNT newest prices. The number 2 changes so it can not be hard-coded.

This can be broken out into a FOR SELECT loop and break when a count is reached. Is that the best way though? Can this be done in a single SQL statement?

Creating the SQL as a string and running it is not the best fit either. It is important that the database compile my SQL statement.

Solution

  • You don't have to use CTE, you can do it directly:

    select avg(price) from (
  select first :cnt p.Price
  from Price p
  order by p.Date desc
);