CREATE TABLE t1(id INTEGER PRIMARY KEY,name VARCHAR(64));
CREATE TABLE t2(id INTEGER,time INTEGER,deleted INTEGER(1) PRIMARY KEY(id,time));
My question is, how can I: "select t1.name,t2.deleted from t1 join t2 using id" but only keep each row where t2.time is the max for that id?
To put that another way, is there a single select I can issue which does the equivalent of doing:
CREATE TEMPORARY TABLE temp(id INTEGER,time INTEGER);
INSERT INTO temp (id,time) SELECT id,MAX(time),deleted FROM t2 GROUP BY id;
SELECT t1.name,t2.deleted FROM temp JOIN t1 USING id JOIN t2 USING id WHERE t2.time=temp.time;
I am hoping for some syntax that is the valid SQL/SQLite equivalent of: SELECT t1.name,t2.deleted FROM t1 join t2 USING id WHERE t2.time=MAX(t2.time)
thanks for any help.
Michael Hunley
Senior Engineer
PocketPurchase, Inc.
--------------------------------------------------------------------- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
