title: How to select k-th record per field in a single SQL query
body: <p>please help me with the following problem. I have spent already one week trying to put all the logic into one SQL query​ but still got no elegant result. I hope the SQL experts could give me a hint,</p>
  
  <p>I have a table which has 4 fields: date, expire_month, expire_year and value. The primary key is defined on 3 first fields. Thus for a concrete date few values are present with different expire_month, expire_year. I need to chose one value from them for every date, present in the table.</p>
  
  <p>For example, when I execute a query:</p>
  
  <pre class="lang-sql prettyprint-override"><code>SELECT date, expire_month, expire_year, value FROM futures 
  WHERE date = ‘1989-12-01' ORDER BY expire_year, expire_month;
  </code></pre>
  
  <p>I get a list of values for the same date sorted by expirity (months are coded with letters):</p>
  
  <pre><code>1989-12-01  Z   1989    408.25
  1989-12-01  H   1990    408.25
  1989-12-01  K   1990    389
  1989-12-01  N   1990    359.75
  1989-12-01  U   1990    364.5
  1989-12-01  Z   1990    375
  </code></pre>
  
  <p>The correct single value for that date is the k-th record from top. For example, of k is 2 then the «correct single» record would be:</p>
  
  <pre><code>1989-12-01  H   1990    408.25
  </code></pre>
  
  <p>How can I select these «correct single» values for every date in my table?</p>