Title:

>select id1, max(id2) from table group by id1 SLOW

Query:

>select cust_id, min(fcstgentimestamp) AS maxgentimestampforcust
> from forecast
> group by cust_id

Hi Tim,
do you mean min(fcstgentimestamp) or max(fcstgentimestamp)? The primer I would expect to be as quick as possible and not do a table scan, but if the latter, you might require a descending index on fcstgentimestamp. Not knowing whether you have 10 customers with 1000000 fcstgentimestamps each or 1000000 customers with 10 fcstgentimestamps each, it is impossible to guess whether such an index would be useful or not. An answer might also depend on which Firebird version you use, e.g. I think an expression index might be a possible solution, but that is recently introduced into Firebird, so if you're on, say, Fb 1.5, then that is not an option.

Also note that the more common way for primary keys to be defined in Firebird, would be on a single field with no meaning attached (e.g. add a field PK_FORECAST that you typically fill through a BEFORE INSERT trigger accessing a generator). That makes changes easier, e.g. if you at one point in time discover that it would be good to be able to store more than one row with the same CUST_ID, FCSTGENTIMESTAMP, RUNOUTTIMESTAMP and DEMAND or want to change the field definition for one of those fields.

HTH,
Set