title: Guidance on using the WITH clause in SQL
body: <p>I understand how to use the <code>WITH</code> clause for recursive queries (!!), but I'm having problems understanding its general use / power.</p>
  
  <p>For example the following query updates one record whose id is determined by using a subquery returning the id of the first record by timestamp:</p>
  
  <pre><code>update global.prospect psp
  set    status=status||'*'
  where  psp.psp_id=(
             select  p2.psp_id
             from    global.prospect p2
             where   p2.status='new' or p2.status='reset'
             order   by p2.request_ts
             limit   1 )
  returning psp.*;
  </code></pre>
  
  <p>Would this be a good candidate for using a <code>WITH</code> wrapper instead of the relatively ugly sub-query? If so, why?</p>