title: selecting one row per data with aggregate functions
body: <p>I have a table:</p>
  
  <pre><code>         price
  ---------------------
          id | int (PK)
  unit_price | number
     part_id | int (FK)
        line | int
  </code></pre>
  
  <p>With a unique constraint on <code>part_id</code> and <code>line</code> (so parts can have multiple prices differentiated by the "line")</p>
  
  <p>To visualize:</p>
  
  <pre><code>id | unit_price | part_id | line
  --------------------------------
   1 |     100.00 |       1 |    1
   2 |     150.00 |       1 |    2
   3 |      90.00 |       1 |    3
   --------------------------------
   4 |      75.00 |       2 |    1
   5 |      70.00 |       2 |    2
   --------------------------------
   6 |      90.00 |       3 |    1
   7 |      95.00 |       3 |    2
  </code></pre>
  
  <p>I need to write a query that will take the <em>lowest</em> 'line' for each part and show the price, so the output will be this:</p>
  
  <pre><code>id | unit_price | part_id | line
  --------------------------------
   1 |     100.00 |       1 |    1
   4 |      75.00 |       2 |    1
   6 |      90.00 |       3 |    1
  </code></pre>
  
  <p>I started with this query:</p>
  
  <pre><code>SELECT min(line), unit_price, part_id
  FROM price
  GROUP BY ... ?
  </code></pre>
  
  <p>But I quickly realized that if I add <code>unit_price</code> and <code>part_id</code> to the <code>GROUP BY</code> clause, then it will basically give me the original output (everything) because each <code>unit_price</code> is unique. But I can't <code>GROUP BY part_id</code> by itself, because <code>unit_price</code> needs to be included in the <code>GROUP BY</code> clause.</p>
  
  <p>Should I even be using the aggregate <code>min</code> function to select only <em>one</em> row per <code>part_id</code>? I think I might be barking up the wrong tree with <code>GROUP BY</code>. I only present it in the question to show the things I've tried.</p>
  
  <p>In my dataset, not all prices are guaranteed to start with '1', so a <code>= 1</code> condition won't work. I need the minimum. Also, I'm using MySQL, but I think that perhaps shouldn't matter.</p>