Skip to content
Advertisement

How to autofill a column with sequential number, but based on a parameter?

I’m trying to add a new column to few MySQL (MariaDB) tables. I want to make the column auto-filled with sequential number. What I figured out so far is:

SELECT @count:=0;
UPDATE users SET ordering = @count:=@count+1;

It works perfectly. However, I don’t know how to make it so that the order in which the numbers are assigned is based on another value, namely ascending order of another integer field called regdate. Here’s an example.

Current result:

login regdate ordering
user 1633205589 1
guy 16332060000 3
account 16332090000 2
data 16332095000 4

What I want:

login regdate ordering
user 1633205589 1
guy 16332060000 2
account 16332090000 3
data 16332095000 4

I hope it’s pretty clear and concise 🙂

Advertisement

Answer

You can use a joined table with ROW_NUMBER

CREATE TABLE users (
  `login` VARCHAR(7),
  `regdate` VARCHAR(20) ,
  `ordering` INTEGER
);
INSERT INTO users
  (`login`, `regdate`, `ordering`)
VALUES
  ('user', '1633205589', '1'),
  ('guy', '16332060000', '3'),
  ('account', '16332090000', '2'),
  ('data', '16332095000', '4');
UPDATE users  u1
JOIN (SELECT `login`, `regdate`, row_number() over (ORDER BY regdate ASC) rn FROM users) u2 
ON u1.`login` = u2.`login` AND u1.`regdate` = u2.`regdate`
SET u1.ordering =  u2.rn ;
SELECT * FROM users
login   | regdate     | ordering
:------ | :---------- | -------:
user    | 1633205589  |        1
guy     | 16332060000 |        2
account | 16332090000 |        3
data    | 16332095000 |        4

db<>fiddle here

User contributions licensed under: CC BY-SA
1 People found this is helpful
Advertisement