is it possible to set a `order` field with the result of ORDER BY-CodePudding

My table_:

id	label
1	art2
2	art1
3	art4
4	art3

Expected Update:

id	label	order_
1	art2	2
2	art1	1
3	art4	4
4	art3	3

So, I want to set the "order_" field with result of

SELECT * FROM table_ ORDER BY label ASC;

Is it possible with a single request? Or should I just get result and then update "order_" in my app ?

CodePudding user response：

You can't do it directly by using the generic ORDER BY clause, though you can achieve that result if the ORDER BY clause is found within a window function.

SELECT id, 
       label, 
       ROW_NUMBER() OVER(ORDER BY label) AS order_
FROM table_
ORDER BY id

Check the demo here.

If you already have an empty "order" field and you need an UPDATE statement, you can first craft the ROW_NUMBER values separately (inside a subquery), then update your original table by matching on the identifier field.

WITH cte AS (
    SELECT id, ROW_NUMBER() OVER(ORDER BY label) AS rn
    FROM table_
)
UPDATE     table_
INNER JOIN cte ON table_.id = cte.id 
SET order_ = rn;

Check the demo here.