StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POSQL Composite key grouping issue
text
Body
copied!<p>I've got a very frustrating SQL issue which i can't for the life of me solve with a derived query returning a composite key but also performing a MIN() aggregate function on another field within that table. If i was performing the MIN() on one of the composite keys it would be easy, but since i need to return both keys and perform the MIN() function as well to the outer query i can't work out how to do this. The entire query looks like this:</p> <pre><code>SELECT p.name as productname ,tmp.packageid ,tmp.price ,ppk2.packageoptionid ,ppk2.selcomproductid FROM ( SELECT ppk.productid, ppk.packageid, MIN(ppk.price) as price FROM product_package ppk INNER JOIN package pk ON ppk.packageid = pk.id INNER JOIN [plan] pl ON pk.planid = pl.id WHERE pk.networkid = 1 GROUP BY ppk.productid, ppk.packageid ) tmp INNER JOIN product_package ppk2 ON ( ppk2.productid = tmp.productid AND ppk2.packageid = tmp.packageid ) INNER JOIN product p ON (p.id = ppk2.productid) WHERE p.isenabled = 1; </code></pre> <p>Current Results:</p> <pre><code>-------------------------------------- productid | packageid | price 1 500 0 1 501 19.95 1 502 29.95 2 501 0 3 500 15 3 504 39.95 </code></pre> <p>Desired Results:</p> <pre><code>-------------------------------------- productid | packageid | price 1 500 0 2 501 0 3 500 15 </code></pre> <p>The derived query "tmp" is where my issue lies as i need a unique rows back for each product/package combination with the lowest price, before joining onto the outer tables.</p> <p>Any help would be greatly appreciated!</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