> The query for "latest price for each product" goes like this (which I > can grasp quite easily): > > SELECT * FROM price p1 > WHERE ctime=(SELECT MAX(ctime) FROM price p2 WHERE > p1.product_id=p2.product_id) > > or: > > SELECT * FROM price p1 > WHERE NOT EXISTS (SELECT * FROM price p2 WHERE > p1.product_id=p2.product_id AND p1.ctime < p2.ctime) > > but how do yo query for "latest 3 prices for each product"? The > "price" table contains current price as well as price histories.
Will this work? SELECT * FROM price p1 WHERE ctime int (SELECT ctime FROM price p2 WHERE p1.product_id=p2.product_id order by ctime desc limit 3) -- regards Claus When lenity and cruelty play for a kingdom, the gentlest gamester is the soonest winner. Shakespeare -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
