title: Laravel Eloquent/Fluent select top 1 row per group
body: <p>I need to determine if a given row has a <code>signed_out</code> or <code>signed_in</code> date that is older than one year. Sometimes a territory may be signed out currently which is specified by a <code>NULL</code> value. If this is the case, I need to look to the next highest <code>signed_in</code> value to determine if the territory has been signed out within a year. Otherwise, I need to look at the last <code>signed_in</code> date per group to determine this.</p>
  
  <p>The groups are based on the <code>territory_id</code> column.</p>
  
  <p>My table:</p>
  
  <pre><code>-----------------------------------------------------------------
  | id | territory_id | signed_out          | signed_in           |
  -----------------------------------------------------------------
  | 5  | 8            | 2012-12-09 00:00:00 | 2013-02-28 00:00:00 |
  | 9  | 8            | 2014-03-23 14:48:17 | 2014-06-11 00:00:00 |
  | 11 | 10           | 2014-06-25 11:07:59 | NULL                |
  | 14 | 10           | 2012-06-27 21:28:17 | 2012-10-10 00:00:00 |
  | 15 | 11           | 2014-06-27 21:28:48 | NULL                |
  | 13 | 11           | 2012-09-12 20:23:07 | 2012-12-27 00:00:00 |
  | 20 | 11           | 2011-10-30 21:05:29 | 2012-01-05 00:00:00 |
  -----------------------------------------------------------------
  </code></pre>
  
  <p>Expected Results: Rows <code>9</code>, <code>11</code>, <code>15</code></p>
  
  <p>I've looked at many examples of <code>group by</code> and <code>greatest-n-per-group</code> tagged mysql questions but I can't wrap my head around how to build this query, especially in <code>Laravel</code> using Eloquent or Fluent. If I have to use a Raw query, that's fine I guess.</p>
  
  <p>What I've tried:</p>
  
  <pre><code>TerritoriesSignInOut::where('signed_in', '&lt;=', Carbon::now()-&gt;subYear())
                      -&gt;orWhere(function($query)
                      {
                          $query-&gt;where('signed_out', '&lt;=', Carbon::now()-&gt;subYear())
                                -&gt;whereNull('signed_in');
                      })
                      -&gt;groupBy('territory_id')
                      -&gt;orderBy('signed_in', 'DESC')
                      -&gt;get();
  </code></pre>
  
  <p>but that doesn't perform the right subquery select which is what I think the issue is with that statement.</p>
  
  <p>Any help is greatly appreciated as I've been racking my brain over this for hours.</p>