Alfred -



I believe that the following will work:



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



Watch out for the fact that 1.1.1.2 is greater than 1.1.1.10, though. Using
fixed field numeric representations within your version as a string is one
way around this.



Have fun.



Louis Kleiman

SSTMS, Inc.



-----Original Message-----
From: Alfred Thomas [mailto:alfred@...]
Sent: Thursday, December 18, 2003 10:01 AM
To: firebird-support@yahoogroups.com
Subject: [firebird-support] Select query



Hi

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 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

Thanks for all the help

Regards
Alfred



To unsubscribe from this group, send an email to:
firebird-support-unsubscribe@yahoogroups.com








Yahoo! Groups Sponsor



ADVERTISEMENT

<http://rd.yahoo.com/SIG=12cf4g27g/M=258297.4271147.5470572.4049140/D=egroup
web/S=1705115386:HM/EXP=1071846031/A=1683962/R=0/*http:/www.techsmith.com/rd
r/ban/syah/default.asp> click here



<http://us.adserver.yahoo.com/l?M=258297.4271147.5470572.4049140/D=egroupmai
l/S=:HM/A=1683962/rand=711462347>



_____

Yahoo! Groups Links

* To visit your group on the web, go to:
http://groups.yahoo.com/group/firebird-support/


* To unsubscribe from this group, send an email to:
firebird-support-unsubscribe@yahoogroups.com
<mailto:firebird-support-unsubscribe@yahoogroups.com?subject=Unsubscribe>


* Your use of Yahoo! Groups is subject to the Yahoo! Terms of
<http://docs.yahoo.com/info/terms/> Service.



[Non-text portions of this message have been removed]