title: Get row details when selecting minimal value in mysql query
body: <p>I'm using the following query, to select the lowest price in the database, along with all the additional data needed.</p>
  
  <pre><code>SELECT 
      p1.id as pid, p1.persons, p1.skipas_incl, temp.* 
  FROM
      winter_accommodaties_prijzen p1
  INNER JOIN (
      SELECT
          a.id as accom_id, p.vertrekdatum, Min(p.prijs) as prijs, a. max_personen, a.naam as naam, g.pistes_groen+g.pistes_blauw+g.pistes_rood+g.pistes_zwart as pistes, liften_type1+liften_type2+liften_type3+liften_type4 as liften, g.naam as gebied, g.lid, d.gid, d.naam as dorp, d.hoogte_dorp, d.afstand_utrecht, d.dalafdaling, g.min_hoogte, g.max_hoogte, t.naam as typenaam, afstand_piste
      FROM
          winter_accommodaties_prijzen p
          INNER JOIN winter_accommodaties a ON (a.id = p.accid)
          INNER JOIN winter_dorpen d ON (d.id = a.did)
          INNER JOIN winter_gebied g ON (g.id = d.gid)
          INNER JOIN winter_accommodaties_types t ON (a.tid = t.id)
      WHERE
          g.lid IN(1,2) AND (p.vertrekdatum BETWEEN '2016-08-30' AND '2017-06-30' OR p.vertrekdatum = '0000-00-00') AND g.max_hoogte &gt;= 500 AND (g.pistes_groen+g.pistes_blauw+g.pistes_rood+g.pistes_zwart) &gt;= 50 AND d.hoogte_dorp &gt;= 500 AND d.afstand_utrecht &lt;= 2500 AND prijs &lt; 1200 AND p.persons &gt;= 4
      GROUP BY
          p.accid
      ORDER BY 
          prijs, naam ASC
      LIMIT
          0, 10) AS temp
  ON 
      temp.accom_id = p1.accid AND 
      temp.prijs = p1.prijs AND
      temp.vertrekdatum = p1.vertrekdatum
  GROUP BY
      p1.accid
  </code></pre>
  
  <p>The SELECT between brackets selects the various fields needed and the lowest price. The 'vertrekdatum' which is a date used in the parent query to get the correct price_id. This as a price/accommodation can have multiple leave dates, hence the combination between </p>
  
  <pre><code>price&lt;-&gt;ID&lt;-&gt;date
  </code></pre>
  
  <p>The problem currently is, is that the date selected (and possible other fields also), are not perse linked to the row with the MIN(p.prijs), resulting in an error in the parent query e.g. it only shows a selection on which it accidentally goes right.</p>
  
  <p>I'm struggling on how to resolve this issue, as I have tried various options/linkages between tables, but don't get the expected outcome. Could you guys help me on a suggestion how to improve this query so it provides the expected outcome?</p>
  
  <p><strong>Solution</strong></p>
  
  <p>After some testing, playing around and lots of searching, the following is the outcome which works for me:</p>
  
  <pre><code>SELECT              
      subset.*, a.max_personen, a.naam as naam
  FROM                
      (
      SELECT
          temp.*,
          @rowNum := if(@prevValue = temp.accom_id, @rowNum+1, 1) as row_num, 
          @prevValue := temp.accom_id as accid1
      FROM
          (
          SELECT
              p.id as pid, p.accid as accom_id, p.prijs, p.vertrekdatum,   p.persons, p.skipas_incl, g.pistes_groen+g.pistes_blauw+g.pistes_rood+g.pistes_zwart as pistes, g.liften_type1+g.liften_type2+g.liften_type3+g.liften_type4 as liften, g.naam as gebied, g.lid, d.gid, d.naam as dorp, d.hoogte_dorp, d.afstand_utrecht, d.dalafdaling, g.min_hoogte, g.max_hoogte, t.naam as typenaam, a.afstand_piste                    
          FROM                        
              winter_accommodaties_prijzen p                      
              INNER JOIN winter_accommodaties a ON (a.id = p.accid)                       
              INNER JOIN winter_dorpen d ON (d.id = a.did)                        
              INNER JOIN winter_gebied g ON (g.id = d.gid)                        
              INNER JOIN winter_accommodaties_types t ON (a.tid = t.id)                   
          WHERE                       
              g.lid IN(1,2) 
              AND (p.vertrekdatum BETWEEN '2016-09-16' AND '2017-05-16' OR   p.vertrekdatum = '0000-00-00') 
              AND g.max_hoogte &gt;= 500 
              AND (g.pistes_groen+g.pistes_blauw+g.pistes_rood+g.pistes_zwart) &gt;= 50
              AND d.hoogte_dorp &gt;= 500 
              AND d.afstand_utrecht &lt;= 2500
              AND prijs &lt; 1200 
              AND p.persons &gt;= 4                  
          ORDER BY                         
              p.accid, p.prijs ASC, p.vertrekdatum) temp,             
          (SELECT @rowNum := 1 FROM DUAL) X,              
          (SELECT @prevValue := -1 FROM DUAL) Y) subset           
  INNER JOIN winter_accommodaties a ON (a.id = subset.accom_id)           
  WHERE               
      subset.row_num = 1           
  ORDER BY 
      prijs, naam ASC         
  LIMIT               
      0, 10
  </code></pre>