Update top N values using PostgreSQL

WITH cte AS (   SELECT id, row_number() OVER (ORDER BY account DESC NULLS LAST) AS rn   FROM   accountrecords       ORDER  BY account DESC NULLS LAST   LIMIT  10   )UPDATE accountrecords aSET    accountrank = cte.rnFROM   cteWHERE  cte.id = a.id;

Joining in a table expression is typically faster than correlated subqueries. It is also shorter.

With the window function row_number() distinct numbers are guaranteed. Use rank() (or possibly dense_rank()) if you want rows with equal values for account to share the same number.

Only if there can be NULL values in account, you need to append NULLS LAST for descending sort order, or NULL values sort on top:

• PostgreSQL sort by datetime asc, null first?

If there can be concurrent write access, the above query is subject to a race condition. Consider:

• Atomic UPDATE .. SELECT in Postgres
• Postgres UPDATE … LIMIT 1

However, if that was the case, the whole concept of hard-coding the top ten would be a dubious approach to begin with.

Use a CTE instead of a plain subquery (like I had at first) to enforce the LIMIT reliably. See links above.

Sure, you can use your select statement in a subquery. Generating the rank-order isn't trivial, but here's at least one way to do it. I haven't tested this, but off the top of my head:

update accountrecordsset accountrank =    (select count(*) + 1 from accountrecords r where r.account > account)where id in (select id from accountrecords order by account desc limit 10);

This has the quirk that if two records have the same value for account, then they will get the same rank. You could consider that a feature... :-)