Hi all, and thanks in advance for you help.
I have the following schema CREATE TABLE [Scans] (ScanID INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT ,Timestamp DATETIME NOT NULL UNIQUE ,EndTime DATETIME NOT NULL DEFAULT CURRENT_TIME ,Result VARCHAR ); CREATE TABLE [Images] (ImageID INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT ,ScanID INTEGER NOT NULL REFERENCES [Scans] DEFERRABLE INITIALLY DEFERRED ,Filename VARCHAR NOT NULL ,No INTEGER NOT NULL ,X REAL NOT NULL DEFAULT 0.0 ,Y REAL NOT NULL DEFAULT 0.0 ,Z REAL NOT NULL DEFAULT 0.0 ,R INTEGER NOT NULL DEFAULT 0 ,C INTEGER NOT NULL DEFAULT 0 ,wMicrons REAL NOT NULL DEFAULT 0.0 ,hMicrons REAL NOT NULL DEFAULT 0.0 ,wPixels INTEGER NOT NULL DEFAULT 0 ,hPixels INTEGER NOT NULL DEFAULT 0 ,UNIQUE (ScanID, Filename, No)); CREATE INDEX Images_ScanID_Index on Images(ScanID); CREATE TABLE [Analyzers] (AnalyzerID INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT ,Name VARCHAR NOT NULL UNIQUE ); CREATE TABLE [Analysis] (AnalysisID INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT ,AnalyzerID INTEGER NOT NULL REFERENCES [Analyzers] DEFERRABLE INITIALLY DEFERRED ,ScanID INTEGER NOT NULL REFERENCES [Scans] DEFERRABLE INITIALLY DEFERRED ,Timestamp DATETIME NOT NULL ,EndTime DATETIME NOT NULL DEFAULT CURRENT_TIME ,NumDefects INTEGER DEFAULT 0 ,Result VARCHAR ); CREATE INDEX Analysis_AnalyzerID_Index on Analysis(AnalyzerID); CREATE INDEX Analysis_ScanID_Index on Analysis(ScanID); CREATE TABLE [Defects] (DefectID INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT ,ImageID INTEGER NOT NULL REFERENCES [Images] DEFERRABLE INITIALLY DEFERRED ,AnalysisID INTEGER NOT NULL REFERENCES [Analysis] DEFERRABLE INITIALLY DEFERRED ,X REAL NOT NULL DEFAULT 0.0 ,Y REAL NOT NULL DEFAULT 0.0 ,W REAL NOT NULL DEFAULT 0.0 ,H REAL NOT NULL DEFAULT 0.0 ,Area REAL NOT NULL DEFAULT 0.0 ); And this select which I would like to modify to only return lowest X,Y value for each ImageID. select Defects.DefectID , Defects.ImageID , Defects.AnalysisID , Defects.X , Defects.Y , Defects.W , Defects.H , Defects.Area , Images.X + Defects.X as DefectX , Images.Y + Defects.Y as DefectY from Defects join Images on Defects.ImageID = Images.ImageID I believe that the data is all stored such that the first stored defect for each ImageID will have the lowest X,Y values. At least for now this assumption is probably good enough. Jeff Archer Nanotronics Imaging jsarc...@nanotronicsimaging.com <330>819-4615 _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
