StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>This query should return the specified resultset (this is only desk checked, not tested)</p> <p><strong>to return ALL product_id</strong></p> <pre><code>SELECT p.product_id , m.order_d , m.order_timestamp , m.order_status FROM products p LEFT JOIN ( SELECT kl.product_id , MAX(ko.order_timestamp) AS latest_timestamp FROM orderproductsmap kl JOIN orders ko ON ko.order_id = kl.order_id GROUP BY kl.product_id ) l ON l.product_id = p.product_id LEFT JOIN ( SELECT ml.product_id , mo.order_id , mo.order_timestamp , mo.order_status FROM orderproductsmap ml JOIN orders mo ON mo.order_id = ml.order_id ) m ON m.product_id = l.product_id AND m.order_timestamp = l.latest_timestamp GROUP BY p.product_id </code></pre> <p>The inline view "<code>l</code>" gets us the latest "<code>order_timestamp</code>" for each "<code>product_id</code>". This is joined to inline view "<code>m</code>" to get us the whole row for the order that has the latest timestamp.</p> <p>If there happens to be more than one order with the same latest "<code>order_timestamp</code>" (i.e. <code>order_timestamp</code> is not guaranteed to be unique for a given <code>product_id</code>) then the outermost <code>GROUP BY</code> ensures that only one of those order rows is returned.</p> <p>If only particular product_id values need to be returned, add a WHERE clause in the outermost query. For performance, that same predicate can be repeated in the inline views.</p> <p><strong>to return only SPECIFIC product_id</strong> we add three WHERE clauses:</p> <pre><code>SELECT p.product_id , m.order_d , m.order_timestamp , m.order_status FROM products p LEFT JOIN ( SELECT kl.product_id , MAX(ko.order_timestamp) AS latest_timestamp FROM orderproductsmap kl JOIN orders ko ON ko.order_id = kl.order_id WHERE kl.product_id IN (8,9,10,12,13,14,15,32,83) GROUP BY kl.product_id ) l ON l.product_id = p.product_id LEFT JOIN ( SELECT ml.product_id , mo.order_id , mo.order_timestamp , mo.order_status FROM orderproductsmap ml JOIN orders mo ON mo.order_id = ml.order_id WHERE ml.product_id IN (8,9,10,12,13,14,15,32,83) ) m ON m.product_id = l.product_id AND m.order_timestamp = l.latest_timestamp WHERE p.product_id IN (8,9,10,12,13,14,15,32,83) GROUP BY p.product_id </code></pre> <p>Only the WHERE clause on the outermost query is required. The other two are added just to improve performance by limiting the size of each of the derived tables.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload