>hi all, i have this table
>
>TABLE DATA
>(
> ID bigint NOT NULL,
> TIME timestamp NOT NULL,
> SOURCE_ID bigint,
> VALUE double precision NOT NULL,
>);
>
>i need to do a query or a procedure to group data by SOURCE_ID and to get
>
>SOURCE_ID, Max(time), percentage of rows where value < 5, percentage of rows where value < 10

WITH TMP(SOURCE_ID, MAX_TIME, VALUE5, VALUE10, COUNT_ALL)
AS
(SELECT SOURCE_ID, MAX(TIME), SUM(CASE WHEN VALUE < 5 THEN 1 ELSE 0 END), SUM(CASE WHEN VALUE < 10 THEN 1 ELSE 0 END), COUNT(*)
FROM DATA
GROUP BY 1)

SELECT SOURCE_ID, MAX_TIME, (VALUE5*100)/COUNT_ALL, (VALUE10*100)/COUNT_ALL
FROM TMP

>my table have a lot of data (about 100 milions of record) so i need also that the query is fastest

The only way to make a query quick is to ascertain that it doesn't involve a lot of rows. It doesn't matter if the table contains 100 million rows if there's only 10 rows with SOURCE_ID 100 and you put WHERE SOURCE_ID = 100 in the where clause of the CTE above (provided SOURCE_ID is indexed, of course), but without such a where clause things will be slow (Firebird has to examine every row that contributes to the answer regardless of the value of VALUE, possibly even several versions of each row).

HTH,
Set