title: Postgres UPDATE using rank window function
body: <p>I have a table called <code>medias</code> where I've just recently added a new column to called <code>sort_order</code> of type <code>Int</code>.</p>
  
  <p>The row values will not be unique to the entire table, but to their own respective <code>owner_user_id</code> fields. Regardless, I don't even care about the uniqueness of them tbh.</p>
  
  <p>The point of all this is to allow users to set the sort order of the photos they upload (up to 10, and they can drag and re-order, etc). When the user "deletes" a photo, I don't remove the record, I simply set a <code>visible</code> field to <code>false</code> on that row.</p>
  
  <p>Aaaanyway, I'm introducing a migration that adds <code>sort_order</code> (they used to not be able to order the photos, they would just be sorted according to <code>order by created_at asc</code>).</p>
  
  <p>Since adding the new field, I've made the new <code>sort_order</code> have a default value of <code>10</code> (so that it's backwards compatible for folks who haven't updated the application).</p>
  
  <p>I was able to come up with this query:</p>
  
  <pre><code>select
    owner_user_id, 
    sort_order, rank() over (PARTITION BY owner_user_id ORDER BY sort_order asc, created_at asc) as new_sort_order 
  from medias 
  where visible=true 
  order by sort_order asc, created_at asc;
  </code></pre>
  
  <p>This spits out something that looks like the following:</p>
  
  <pre><code> owner_user_id | sort_order | new_sort_order
  ---------------+------------+---------------
              76 |         10 |      1
              76 |         10 |      2
              76 |         10 |      3
              76 |         10 |      4
              76 |         10 |      5
               9 |         10 |      1
               9 |         10 |      2
               9 |         10 |      3
               9 |         10 |      4
               9 |         10 |      5
              79 |         10 |      1
              79 |         10 |      2
              87 |         10 |      1
              87 |         10 |      2
              87 |         10 |      3
              85 |         10 |      1
              90 |         10 |      1
              90 |         10 |      2
              90 |         10 |      3
  </code></pre>
  
  <p>at this point all i really want to do is set that <code>sort_order</code> to that <code>rank()</code>. Any thoughts on how to do this?</p>