title: Select unique values sorted by date
body: <p>I am trying to solve an interesting problem.  I have a table that has, among other data, these columns (dates in this sample are shown in European format - dd/mm/yyyy):</p>
  
  <pre><code>n_place_id   dt_visit_date
  (integer)    (date)
  ==========   =============
     1           10/02/2012
     3           11/03/2012
     4           11/05/2012
    13           14/06/2012
     3           04/10/2012
     3           03/11/2012
     5           05/09/2012
    13           18/08/2012
  </code></pre>
  
  <p>Basically, each place may be visited multiple times - and the dates may be in the past (completed visits) or in the future (planned visits).  For the sake of simplicity, today's visits are part of planned future visits.</p>
  
  <p>Now, I need to run a select on this table, which would pull <strong>unique</strong> place IDs from this table (without date) sorted in the following order:</p>
  
  <ol>
  <li>Future visits go before past visits</li>
  <li>Future visits take precedence in sorting over past visits for the same place</li>
  <li>For future visits, the earliest date must take precedence in sorting for the same place</li>
  <li>For past visits, the latest date must take precedence in sorting for the same place.</li>
  </ol>
  
  <p>For example, for the sample data shown above, the result I need is:</p>
  
  <pre><code> 5     (earliest future visit)
   3     (next future visit into the future)
  13     (latest past visit)
   4     (previous past visit)
   1     (earlier visit in the past)
  </code></pre>
  
  <p>Now, I can achieve the desired sorting using <code>case when</code> in the <code>order by</code> clause like so:</p>
  
  <pre><code>select
      n_place_id
  from
      place_visit
  order by
      (case when dt_visit_date &gt;= now()::date then 1 else 2 end),
      (case when dt_visit_date &gt;= now():: date then 1 else -1 end) * extract(epoch from dt_visit_date)
  </code></pre>
  
  <p>This <em>sort of</em> does what I need, but it does contain repeated IDs, whereas I need unique place IDs.   If I try to add <code>distinct</code> to the select statement, postgres complains that I must have the <code>order by</code> in the select clause - but then the unique won't be sensible any more, as I have dates in there.</p>
  
  <p>Somehow I feel that there should be a way to get the result I need in one select statement, but I can't get my head around how to do it.</p>
  
  <p>If this can't be done, then, of course, I'll have to do the whole thing in the code, but I'd prefer to have this in one SQL statement.</p>
  
  <p>P.S. I am not worried about the performance, because the dataset I will be sorting is not large.  After the <code>where</code> clause will be applied, it will rarely contain more than about 10 records.</p>