Postgres UPDATE using rank window function
<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>