Subject Re: [firebird-support] Help with query?
Author Helen Borrie
At 08:15 AM 23/07/2006, you wrote:
>I'm not good enough with SQL to solve a problem I'm having with a
>query. I'm hoping someone can help point me in the right direction. It
>doesn't seem like it should be that hard, but it's beyond me. I'll
>simplify the question to it's bare essentials.
>Take a simple table with 4 columns (Timestamp, VersionNumber, Client,
>Speed) representing clients downloading new versions of our software
>and what download rates they're getting. I'd like a query that returns
>the rows representing the first time each client downloaded each
>version. If a client does not download a particular version there
>would be no rows for those. A client may download a version several
>times but I want only the first of those downloads (smallest
>timestamp). There will be many versions of software.
>Can anyone help me with this? Thanks!

Assuming you want it by version number and you want to include the Speed:

from Yourtable yt1
where yt1.ATimestamp = (select min (yt2.ATimestamp)
from Yourtable yt2
where yt2.VersionNumber = yt1.VersionNumber
and yt2.Client = yt1.Client)
and (add other search arguments if you don't want the whole table)

Make sure you have an ascending index on Atimestamp and a suitable
index on Client.