Creating a sequence column with ORDER BY using MySql 5.5.62

Hi,

I’m inputting the following query using MySql 5.5.62 to creating a sequence column with ORDER BY using MySql 5.5.62

The upgrading to MySQL 8 it depends on the hosting provider and on this moment it’s no possibile.

What I’m trying to achieve is to by adding a sequence column that I can sort my results on instead of sorting on multiple columns. It’s based on an ORDER BY clause involving 1 column.

UPDATE `dotable` t
JOIN (
    SELECT
        ( @rn := @rn + 1 ) AS pPrio,
        pDateHH,
        sID 
    FROM
        `dotable`,
        ( SELECT @rn := 0 ) AS x 
    ORDER BY
        pDateHH ASC 
    ) q ON t.sID = q.sID 
    SET t.pPrio = q.pPrio
    WHERE DATE(t.pDateHH) = DATE_SUB(CURRENT_DATE,INTERVAL 1 DAY);

I need this result

+-------+---------------------+
| pPrio | pDateHH             |
+-------+---------------------+
|     1 | 2021-09-16 18:40:02 |
|     2 | 2021-09-16 19:00:20 |
|     3 | 2021-09-16 19:20:47 |
|     4 | 2021-09-16 20:00:59 |
|     5 | 2021-09-16 20:01:48 |
|     6 | 2021-09-16 20:20:31 |
|     7 | 2021-09-16 20:40:05 |
|     8 | 2021-09-16 21:17:27 |
|     9 | 2021-09-16 21:20:03 |
|    10 | 2021-09-16 21:40:24 |
|    11 | 2021-09-16 22:00:09 |
|    12 | 2021-09-16 22:38:03 |
|    13 | 2021-09-16 22:40:03 |
|    14 | 2021-09-16 23:02:25 |
|    15 | 2021-09-16 23:20:02 |
|    16 | 2021-09-16 23:58:58 |
|     1 | 2021-09-17 00:00:06 |
|     2 | 2021-09-17 00:22:53 |
|     3 | 2021-09-17 00:40:05 |
|     4 | 2021-09-17 01:00:44 |
|     5 | 2021-09-17 01:20:04 |
+-------+---------------------+

Instead of

+-------+---------------------+
| pPrio | pDateHH             |
+-------+---------------------+
|     1 | 2021-09-16 18:40:02 |
|     2 | 2021-09-16 19:00:20 |
|     3 | 2021-09-16 19:20:47 |
|     4 | 2021-09-16 20:00:59 |
|     5 | 2021-09-16 20:01:48 |
|     6 | 2021-09-16 20:20:31 |
|     7 | 2021-09-16 20:40:05 |
|     8 | 2021-09-16 21:17:27 |
|     9 | 2021-09-16 21:20:03 |
|    10 | 2021-09-16 21:40:24 |
|    11 | 2021-09-16 22:00:09 |
|    12 | 2021-09-16 22:38:03 |
|    13 | 2021-09-16 22:40:03 |
|    14 | 2021-09-16 23:02:25 |
|    15 | 2021-09-16 23:20:02 |
|    16 | 2021-09-16 23:58:58 |
|    17 | 2021-09-17 00:00:06 |
|    18 | 2021-09-17 00:22:53 |
|    19 | 2021-09-17 00:40:05 |
|    20 | 2021-09-17 01:00:44 |
|    21 | 2021-09-17 01:20:04 |
+-------+---------------------+

Any suggestion?

My table structure and the update query below on db-fiddle.com , which offers MySQL 5

Thanks in advance for any help.

1 Like

Maybe not the answer you want to hear, but for me its a bad design to insert redundant data in a table. Why not order by multiple columns?
What happens if a new entry is added to the table or one deleted? Will you always trigger this query to have the new prior id’s? that’s horrible

This makes those results impossible.

Your desired results have two days included (09/16/2021, 09/17/2021), the where clause only allows for one…