mysql - How to get other columns from a row when using an aggregate function? -

i'm embarrassed i've been trying accomplish hours without success. i've read dozens of similar questions on stackoverflow , tried countless different things, not have enough grasp of sql achieve i'm trying accomplish.

i have 2 tables, products , product_prices. simplicity, suppose following:

products: id    

product_prices: id | p_id | price | date_added 

what need added price, along date price added. so, in other words, each product, need recent price , date_added (along product id, p_id, of course).

if needed recent date , price 1 product id known, can this:

select price, date_added  product_prices p_id = 1 order date_added desc limit 1 

however, type of query not work when need recent date , price all of products.

i believe solution use max() aggregate function in conjunction group by , subquery, cannot work.

here test database on sql fiddle: http://sqlfiddle.com/#!2/881cae/3

i realize there lot of similar questions on here, have read many of them , haven't been able solve problem, appreciate direct instead of crying "duplicate!" , linking post. thank you.

edit: sql fiddle seems down @ moment, here database schema had on there:

create table products (   id int auto_increment primary key,    name varchar(20) );  insert products (   name ) values ('product 1'), ('product 2');   create table product_prices (   id int auto_increment primary key,    p_id int,    price decimal(10,2),   date_added int );  insert product_prices (   p_id,    price,    date_added ) values (1, 1.99, 1000), (1, 2.99, 2000), (1, 3.99, 3000), (1, 4.99, 4000), (1, 5.99, 5000), (1, 6.99, 6000),   (2, 1.99, 1000), (2, 2.99, 2000), (2, 3.99, 3000), (2, 4.99, 4000), (2, 5.99, 5000), (2, 6.99, 6000); 

here how can it:

select pp.* product_prices pp join (     select p_id, max(date_added) max_date     product_prices     group p_id ) x on pp.p_id = x.p_id , pp.date_added = x.max_date 

the idea make set of tuples {p_id, max_date} each product id (that's inner query) , filter product_prices data using these tuples (that's on clause in inner join).

demo on sqlfiddle.