I had a similar problem recently, the way I solved it looks like that
(it might not be pretty, but it works)
SELECT t1.name, t2.deleted
FROM
t2
INNER JOIN
(
SELECT t2.id, MAX(t2.time) AS last_time
FROM t2
GROUP BY t2.id
) AS t3
ON t2.id = t3.id AND t2.time = t3.last_time
INNER JOIN
t1
ON t1.id = t2.id;
Since SQLite support only STATIC inner queries, it's the only way I
found to do it in a single query.
Tell me if it worked for you.
Simon B.
On Fri, 2004-01-23 at 15:07, Michael Hunley wrote:
> I have the following two table defs:
> 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]
>
