My table:
| id | label |
|---|---|
| 1 | art2 |
| 2 | art1 |
| 3 | art4 |
| 4 | art3 |
Expected Output:
| 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 ?
>Solution :
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
Check the demo here.