Posts Update a column based on row_number() or other tables

Update a column based on row_number() or other tables

The problem

On some project I inserted a new column called position so the entries are orderable by the users. On this project users can create categories which have e.g. a title and may have a super/parent category and are owned by the user who created them.

The problem was that I wanted to have an initial alphabetical ordering of the categories and some rules had to apply. For the sorting only categories of the same user are considered and only categories that have a common super category.

The table looked similar to the following, however note that I have left out some columns and changed the data types (e.g. int as id instead of uuid) to make it more redeable.

1A NULLuser_10
2C NULLuser_20
3B NULLuser_20
4BB 1user_10
5AA 1user_10
6CC 2user_20

The solution

update categories
	set position=r.row_id
from (select, row_number() over (partition by c.super_category_id, c.user_ref order by c.title asc) as row_id from categories c) as r
where =;

This sql query was the solution and would result in a table as seen below.

1A NULLuser_11
2C NULLuser_22
3B NULLuser_21
4BB 1user_12
5AA 1user_11
6CC 2user_21

The inner select query divides the query’s result into partions. partition by c.super_category_id, c.user_ref order by c.title asc divides the query’s result set into partions, every partion has a unique super_category_id and user_ref and is in itself ordered alphabetically.

Using row_numer() we get the row number within the partion for every column and update the position column accordingly in the table.


This post is licensed under CC BY 4.0 by the author.