S
Stephen
I have a database with two tables, each with a numerical primary key field
and a name (text) field, joined by a third containing both the primary key
fields from the other two along with an integer containing a unix date to
provide the time of the changes in the relationship between the outer two
plus another numerical field specifying the person who made the change. I
need to construct a query that provides only the latest pairing of the two
outer tables. The only data I want returned are the numerical primary key
fields from the outer tables as specified by the maximum date.
TableA
ItemtypeAID INTEGER Primary Key
ItemTypeAName TEXT
TableC
ItemtypeCID INTEGER Primary Key
ItemTypeCName TEXT
TableB
ItemtypeAID INTEGER Primary Key
ItemtypeCID INTEGER Primary Key
ChangeDate INTEGER Primary Key
PersonnelID INTEGER
I have tried the following but it returns too many records. It returns a
record for each pairing of the outer two and not just the most recent.
SELECT ItemtypeAID, ItemtypeCID, Max(ChangeDate) FROM TableB GROUP BY
ItemtypeAID, ItemtypeCID;
Any suggestions would be most welcome.
and a name (text) field, joined by a third containing both the primary key
fields from the other two along with an integer containing a unix date to
provide the time of the changes in the relationship between the outer two
plus another numerical field specifying the person who made the change. I
need to construct a query that provides only the latest pairing of the two
outer tables. The only data I want returned are the numerical primary key
fields from the outer tables as specified by the maximum date.
TableA
ItemtypeAID INTEGER Primary Key
ItemTypeAName TEXT
TableC
ItemtypeCID INTEGER Primary Key
ItemTypeCName TEXT
TableB
ItemtypeAID INTEGER Primary Key
ItemtypeCID INTEGER Primary Key
ChangeDate INTEGER Primary Key
PersonnelID INTEGER
I have tried the following but it returns too many records. It returns a
record for each pairing of the outer two and not just the most recent.
SELECT ItemtypeAID, ItemtypeCID, Max(ChangeDate) FROM TableB GROUP BY
ItemtypeAID, ItemtypeCID;
Any suggestions would be most welcome.