How to create a column of mysql using another column (answered)

id name updated_at desc_id
1 Bananas 2021-12-06 09:58:59 2
2 Melons 2022-01-10 10:08:57 1
3 Pies 2019-11-07 11:20:48 3

I need to create the column desc_id that takes informations from updated_at and gives back ids in descending order from the oldes to the newest.

I have so little information of mysql that i don’t know where to start.

>Solution :

We can use ROW_NUMBER() here to generate the desc_id sequence:

SELECT id, name, updated_at,
       ROW_NUMBER() OVER (ORDER BY updated_at DESC) desc_id
FROM yourTable

The above assumes you are using MySQL 8+. For earlier versions of MySQL, we can try:

SELECT id, name, updated_at,
       (SELECT COUNT(*) FROM yourTable t2 WHERE t2.updated_at >= t1.updated_at) desc_id
FROM yourTable t1

Leave a Reply