title: Query to find the FIRST AND SECOND largest value from a group
body: <p>i have a query like this:</p>
  <pre><code>SELECT
   DATEPART(year,some_date),
   DATEPART(month,some_date),
   MAX(some_value) max_value
  FROM
   some_table
  GROUP BY
      DATEPART(year,some_date),
      DATEPART(month,some_date)
  </code></pre>
  <p>This returns a table with: <em>year, month, the largest value for the month</em>.</p>
  <p>I would like to modify the query so that i could obtain:
  <em>year, month, the largest value for the month, <strong>the second largest value for the month</strong></em> in each row.</p>
  <p>It seems to me that the well-known solutions like &quot;TOP 2&quot;, &quot;NOT IN TOP 1&quot; or a subselect won't work here.</p>
  <p>(To be really specific - i am using SQL Server 2008.)</p>