--- In firebird-support@yahoogroups.com, "sbussinger" <scottb@...> wrote:

>
> > select client, versionnumber, min(timestamp)
> > from your_table
> > group by client, versionnumber
>
> Thanks Milan! That's close to what I need, but doesn't return the
> speed at which that download occured. Is there any way to get that as
> well?
>
> I really appreciate the help with this!
>

Its possible to get, but it will probably require a join to itself,
and it depends also on whether your business logic allows for the
possibility of multiple of the same timestamp for the same
client/version number. Variables like CURRENT_TIMESTAMP only return a
precision of seconds. In simple terms, it may be possible two get two
different downloads for the same client for the same version number
with the same minimum timestamp, in which case you may have different
rates.

SQL does not handle these sorts of problems well, I would suggest the
simplest way to calculate it is a stored procedure. Cycle through the
distinct Client / Version Number combinations, and inside the for
select loop request the first 1 speed and timestamp ordered by
timestamp. The first 1 guarantees you only get a single
timestamp/speed for a given client/version. You will need an index on
Client/Version or Client/Version/Timestamp to make the operation fast.
(This index would also help the SQL Milan provided).

---

CREATE INDEX IX_FOO ON YOUR_TABLE(SOMECLIENT, SOMEVERSION, SOMETIME);
commit;

set term ^ ;

create procedure foo
returns
(
SomeClient bigint,
SomeVersion bigint,
SomeTime timestamp,
SomeSpeed bigint
)
as
begin
for select distinct
someclient, someversion
from your_table
into :someclient, :someversion
do
begin
for select first 1
SomeTime, SomeSpeed
from your_table
where someclient = :someClient
and someversion = :someversion
order by SomeTime
into :SomeTime, SomeSpeed
do
begin
suspend;
end
end
end
^

set term ; ^

commit;

SELECT * FROM FOO;

---

Adam