Hi, I've got a table "state" which holds states for products, every insert made on this table is inserting values and value called "whenaction" where is the time (as timestamp) of the insert, so table cound look like this:
id|amount|product_id|whenaction 1 50 2 1111115621 2 45 2 1232546487 3 60 1 4564124548 4 54 1 5648721323 5 54 3 8745432188 6 89 1 5454123123
and I need "For each product find record that has been last inserted" :
id|amount|product_id|whenaction 2 45 2 1232546487 4 54 1 5648721323 5 54 3 8745432188
and this does not works... why???
SELECT id,amount, product_id,whenaction FROM state as s1 WHERE whenaction=(SELECT MAX(s2.whenaction) FROM state s2 WHERE s1.product_id = s2.product_id);
Subqueries must be static (evaluated only once) in SQLite. Try this instead:
SELECT id, amount, product_id, whenaction
FROM (SELECT product_id AS pid, MAX(whenaction) AS mx
FROM state
GROUP BY pid) AS w,
state AS s
WHERE s.product_id=w.pid AND s.whenaction=w.mx;If you want this to be efficienct, you need an index like this:
CREATE INDEX idx ON state(product_id, whenaction);
-- D. Richard Hipp -- [EMAIL PROTECTED] -- 704.948.4565
--------------------------------------------------------------------- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
