MySQL 8.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE users
(
    id int(11) AUTO_INCREMENT,
    username varchar(55),
    status int(11),
	PRIMARY KEY(id)
);

INSERT INTO `users` (`id`, `username`, `status`)
  VALUES
  (NULL, 'user1', '1'),
  (NULL, 'user2', '0'),
  (NULL, 'user3', '1'),
  (NULL, 'user4', '0'),
  (NULL, 'user5', '1');

    

Records: 5  Duplicates: 0  Warnings: 0


SELECT * FROM users;


id
username
status


1
user1
1

2
user2
0

3
user3
1

4
user4
0

5
user5
1


SELECT
	@a:=@a+1 as serial_number, username
FROM users
	JOIN (SELECT @a:= 0) a
WHERE status=1;


serial_number
username


1
user1

2
user3

3
user5


SELECT
	@a:=@a+1 as serial_no, username
FROM users
	JOIN (SELECT @a:= 0) a
WHERE status=1 AND username="user3";


serial_no
username


1
user3