clear markdown compare help best fiddles feedback dbanow.uk a leap of faith?
clear markdown donate comments/suggestions/bugs a leap of faith? bible365 diddy dollings
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0. 1926529 fiddles created (21789 in the last week).

CREATE TABLE inventory (the_date date, good text, quantity int); INSERT INTO inventory VALUES ('2013-08-09', 'egg' , 5) , ('2013-08-09', 'pear' , 7) , ('2013-08-02', 'egg' , 1) , ('2013-08-02', 'pear' , 2) ; CREATE TABLE price (the_date date, good text, price numeric); INSERT INTO price VALUES ('2013-08-07','egg' , 120) , ('2013-08-06','pear', 200) , ('2013-08-01','egg' , 110) , ('2013-07-30','pear', 220) ;
4 rows affected
4 rows affected
 hidden batch(es)


-- With DISTINCT ON: SELECT DISTINCT ON (i.good, i.the_date) i.the_date, p.the_date AS pricing_date, i.good, i.quantity, p.price FROM inventory i LEFT JOIN price p ON i.good = p.good AND i.the_date >= p.the_date ORDER BY i.good, i.the_date, p.the_date DESC;
the_date pricing_date good quantity price
2013-08-02 2013-08-01 egg 1 110
2013-08-09 2013-08-07 egg 5 120
2013-08-02 2013-07-30 pear 2 220
2013-08-09 2013-08-06 pear 7 200
 hidden batch(es)


-- With NOT EXISTS (standard SQL, works with every RDBMS I know): SELECT i.the_date, p.the_date AS pricing_date, i.good, i.quantity, p.price FROM inventory i LEFT JOIN price p ON p.good = i.good AND p.the_date <= i.the_date WHERE NOT EXISTS ( SELECT 1 FROM price p1 WHERE p1.good = p.good AND p1.the_date <= i.the_date AND p1.the_date > p.the_date );
the_date pricing_date good quantity price
2013-08-09 2013-08-07 egg 5 120
2013-08-02 2013-08-01 egg 1 110
2013-08-09 2013-08-06 pear 7 200
2013-08-02 2013-07-30 pear 2 220
 hidden batch(es)