Oh, sorry, I can use any firebird version necessary - even 2.5

--- In firebird-support@yahoogroups.com, "tallen342321" <allen.timothy.email@...> wrote:
>
> Dear Set,
>
> Thanks for the reply!
>
> Sorry, I wasn't clear. I was using the min() for testing only as I know that I'd need a descending index in order to use max() (which is what I want to do in the final analysis).
>
> My question is, rather, why is it reading every single row in the table when surely it could use the index that starts with cust_id, fcstgentimestamp to get only the rows it needs. Rows in that index are already orderd by cust_id, fcstgentimestamp, so can't it just look at the first record in the index for each distinct cust_id? (When using MIN()) Or does the index have no concept of going to the next distinct value, only of just going to the next value in the ordered list??
>
> Some stats that you asked for:
>
> - select count(*) from customer = ~50000
> - select count(*) from (select distinct(cust_id) from forecast) = ~30000;
> - There are about 4 million rows in the FORECAST table, and each row is read in the query.
>
> Thanks,
> Tim
>
>
> --- In firebird-support@yahoogroups.com, Svein Erling Tysvær <svein.erling.tysvaer@> wrote:
> >
> > 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
> >
>