Hi Alfred,

> Can anyone please help me to figure out the following query.
> I have the following table
>
> CREATE TABLE PROGRAM (
> "ProgramID" INTEGER NOT NULL,
> "SystemID" SMALLINT NOT NULL,
> "ProgramName" VARCHAR(30),
> "CheckinTimeStamp" TIMESTAMP,
> "SourceDirectory" VARCHAR(150),
> "DeployDirectory" VARCHAR(150),
> "ProgramData" BLOB SUB_TYPE 0 SEGMENT SIZE 8192,
> "FileVersion" VARCHAR(20),
> "VersionComments" VARCHAR(100),
> "StatusCode" SMALLINT NOT NULL
> );

I suggest you get yourself a decent admin/dev tool and drop
those nasty delimited identifiers, just to start with :-)
Or find a setting saying something along the lines of "Uppercase
identifiers".

> I want to select all the different ProgramName's with a specific SystemID
> and StatusCode.
> This will return duplicate ProgramName's with different FileVersions. I

want

> the query to
> select the largest or max FileVersion of each ProgramName like:
>
> Data:
> ProgramName SystemID FileVersion StatusCode
> ----------- -------- ----------- ----------
> Prog1 1 1.0.0.1 1
> Prog1 1 1.0.0.2 1
> Prog2 1 1.0.0.1 1
> Prog3 1 1.0.0.3 1
>
> Query output:
> ProgramName SystemID FileVersion StatusCode
> ----------- -------- ----------- ----------
> Prog1 1 1.0.0.2 1
> Prog2 1 1.0.0.1 1
> Prog3 1 1.0.0.3 1

Try:

select programname, systemid, max(fileversion), statuscode
from ...
group by programname, systemid, statuscode

With regards,

Martijn Tonies
Database Workbench - developer tool for InterBase, Firebird, MySQL & MS SQL
Server.
Upscene Productions
http://www.upscene.com