title: Get the first item of some group in a single SELECT
body: <p>I do something like this <em>every single day</em> - it would be one of my most common queries:</p>
  <pre class="lang-sql prettyprint-override"><code>select * from 
     (select columns, rownumber() over (partition by something) as number from whatever) x
  where x = 1
  </code></pre>
  <p>Which is horrible and seems like there's a lot of extra stuff that shouldn't be needed - a subselect, an alias for the sub select, another select statement - and at best you end up with an extra column in your output - at worst you have to re-list a bunch of columns. </p>
  <p>Obviously, what <em>I want to do</em> is not valid SQL because of the order of operations:</p>
  <pre class="lang-sql prettyprint-override"><code>select * from whatever where rownumber() over (partition by something) = 1
  </code></pre>
  <p>And while this <em>looks</em> like it could work, it actually gives a very wrong answer:</p>
  <pre class="lang-sql prettyprint-override"><code>select * from whatever,
      lateral (select rownumber() over (partition by something) as number)
  where number = 1
  </code></pre>
  <p><code>first_value()</code> seems like it should get you there - but you still end up with lots of rows:</p>
  <pre class="lang-sql prettyprint-override"><code>select first_value(key) over (partition by something), columns from whatever
  </code></pre>
  <p>But I keep thinking that there's a way of achieving it and wasting a lot of time trying.</p>
  <p>So, the <strong>question</strong>, is it possible to achieve it <em><strong>without using a subselect?</strong></em></p>
  <p>Essentially, I want only one of every item matching a single condition, eg., the first item of every order each month, the biggest item in each set, a single test example of each of these different cases etc.</p>