title: MySQL unique record selection approach
body: <p>i have a huge product table, this is its simplified structure:</p>
  <pre><code>ID|SHOP|...|MPN|PRICE|CLICKS
  </code></pre>
  <p>i can have the same product coming from different shops, every shop have its own product ID, but (if the shop support it) the MPN for same products is the same.</p>
  <p>when i query the database for products, order them by clicks, and if there is the same product from multiple source it is showed twice.</p>
  <p>Since im ordered by clicks, the two &quot;equal products&quot; can be far from each other, so its quiete intensive to elaborate it at runtime (consider also the result are paginated and the two equals could be in differente pages).</p>
  <p>i was thinking about a query that (if there are multiple result of the same mpn) duplicate some columns as needed (but them how my software can know about the dynamically created columns), i was thinking about SELF JOIN but i don't know how many shop can carry the same product (maybe i can assume all the shop i use...)</p>
  <p>My main problem is in search (or category) result where i have a list of products with the &quot;BUY AT SHOP FOR PRICE&quot; button that redirect to the shop... my goal is to have more button to BUY if the product (equal by MPN) have duplicate.</p>
  <p>I can keep clicks aligned for both product when a user click on the buy button and maybe it would help</p>
  <p>Product detail page is not a big deal, since i have a single product i can get all other instances of it by MPN and it is quiete easy</p>
  <p>Any suggestion?</p>