Thread: query: last N price for each product?
Dear SQL masters, The query for "latest price for each product" goes like this (which I can grasp quite easily): SELECT * FROM price p1WHERE ctime=(SELECT MAX(ctime) FROM price p2 WHERE p1.product_id=p2.product_id) or: SELECT * FROM price p1WHERE 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. Regards, dave
> 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
David Garamond wrote: > Dear SQL masters, > > 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. Is this what you are looking for... SELECT * FROM price p1 WHERE ctime in (SELECT ctime FROM price p2 WHERE p1.product_id=p2.product_id ORDER BY ctime DESC LIMIT 3)