|Subject||RE: [firebird-support] Select query|
>> I believe that the following will work:CREATE TABLE PROGRAM (
>> SELECT P.ProgramName, P.SystemID, P.StatusCode, MAX(FileVersion)
>> FROM Program P
>> WHERE ((P.SystemID = <Some Value>) AND (P.StatusCode = <Some Value>))
>> GROUP BY 1, 2, 3
>> select programname, systemid, max(fileversion), statuscode
>> from ...
>> group by programname, systemid, statuscode
> "ProgramID" INTEGER NOT NULL,Thanks Louis and Martijn, both queries worked as i wanted and returns the
> "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
How can I also return CheckinTimeStamp, SourceDirectory etc for each entry
(they cannot be used in the group by or aggregate)
What I want is the full details of each PROGRAM where SystemID = ? and
StatusCode = ? and the FileVersion is the latest.
Can I somehow have this select as a sub-select?