Creating a sequence column with ORDER BY using MySql 5.5.62

Databases
1

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
2

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

3

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…

4

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.