Sorting and numbering by time in procedure in mysql-CodePudding

I have unsorted clocks. I want to sort these hours and insert the sequence number to a field. How can I do this in procedure? Thanks.

For example:

example table:

id, time_, row_number
1,'07:57:01',0
2,'07:55:01',0
3,'08:01:21',0
4,'08:05:51',0
5,'08:04:11',0
6,'08:09:21',0

The result i want:

id, time_, row_number
1,'07:57:01',2
2,'07:55:01',1
3,'08:01:21',3
4,'08:05:51',5
5,'08:04:11',4
6,'08:09:21',6

SQLFIDDLE

The ranking is as follows: Select * from exampletable order by row_number;

id, time_, row_number
2,'07:55:01',1
1,'07:57:01',2    
3,'08:01:21',3
5,'08:04:11',4
4,'08:05:51',5
6,'08:09:21',6

How can I get this order in procedure? Thanks.

CodePudding user response：

UPDATE exampletable t1
JOIN ( SELECT id, ROW_NUMBER() OVER (ORDER BY time_ ASC) row_numer
       FROM exampletable ) t2 USING (id)
SET t1.row_numer = t2.row_numer;

https://dbfiddle.uk/?rdbms=mariadb_10.3&fiddle=130beaf4c22fbd7d37cfd65a4aee306f

The column time_ is not defined as unique, hence it can contain duplicated values, and the output for such dataset is not defined. It is possible that you must either expand sorting expression to, for example, ORDER BY time_ ASC, id ASC or use RANK()/DENSE_RANK() instead of ROW_NUMBER().

CodePudding user response：

I just started learning mysql but if you have mysql 8 I think this should work

-- ranking by time (window function)
select 
    id,time_, rank() over (order by time_ ) 
from 
    exampletable 
order by 
     id asc; -- in ascending



id  time_   rank() over (order by time_ )
1   07:57:01    2
2   07:55:01    1
3   08:01:21    3
4   08:05:51    5
5   08:04:11    4
6   08:09:21    6

this should also work on mariadb https://dbfiddle.uk/?rdbms=mariadb_10.3&fiddle=4e345276384189d979409106b77eabee