CODEWITHSUNDEEP
mysql
Edward Sheriff Curtis
Edward Sheriff Curtis

Reputation: 497

Creating a sequence column with ORDER BY using MySql 5.5.62

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

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

Upvotes: 0

Views: 130

Answers (1)

Rahul Biswas
Rahul Biswas

Reputation: 3467

As row_number() does not support lower version so alternative use of row_number in below query. As compare with date so consider a default date.

-- MySQL(5.5 & 5.6)
UPDATE dotable d
JOIN (SELECT @row_no:=CASE WHEN @db_names=DATE(d.pDateHH) THEN @row_no+1 ELSE 1 END AS row_number
     , @db_names:= DATE(d.pDateHH) date_val
    , d.pDateHH
    , d.sID
FROM dotable d,
(SELECT @row_no := 0,@db_names:='1900-01-01') x
ORDER BY DATE(d.pDateHH)) p
   ON d.sID = p.sID
SET d.pPrio = p.row_number
    WHERE DATE(d.pDateHH) = DATE_SUB(CURRENT_DATE,INTERVAL 1 DAY);

Please check from url https://dbfiddle.uk/?rdbms=mysql_5.6&fiddle=f9def1b6a12976bbceb30a6984726ead

Upvotes: 1

Related Questions