MySQL 8.0

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

CREATE TABLE br (
  tid INT,
  rb INT);

INSERT INTO br 
VALUES
(100100,13),
(100100,14),
(100100,15),
(100100,18),
(100100,20),
(100100,21),
(100100,22),
(100100,23),
(100100,25),
(100100,27),
(100100,28),
(100100,31);

Records: 12  Duplicates: 0  Warnings: 0


SELECT z.rank FROM (
SELECT t.rb , t.tid, @rownum := @rownum + 1 AS rank
FROM br t, (SELECT @rownum := 0) r
ORDER BY rb ASC
) as z WHERE tid= '100100'

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'rank
FROM br t, (SELECT @rownum := 0) r
ORDER BY rb ASC
) as z WHERE tid= '10010' at line 2


SELECT z.t_rank FROM (
SELECT t.rb , t.tid, @rownum := @rownum + 1 AS t_rank
FROM br t, (SELECT @rownum := 0) r
ORDER BY rb ASC
) as z WHERE tid= '100100'


t_rank


1

2

3

4

5

6

7

8

9

10

11

12


SELECT * FROM (
SELECT t.rb , t.tid, @rownum := @rownum + 1 AS t_rank
FROM br t, (SELECT @rownum := 0) r
ORDER BY rb ASC
) as z WHERE tid= '100100'


rb
tid
t_rank


13
100100
1

14
100100
2

15
100100
3

18
100100
4

20
100100
5

21
100100
6

22
100100
7

23
100100
8

25
100100
9

27
100100
10

28
100100
11

31
100100
12


SELECT t.rb , t.tid, RANK() OVER (ORDER BY rb) AS t_rank
FROM br t
ORDER BY rb;


rb
tid
t_rank


13
100100
1

14
100100
2

15
100100
3

18
100100
4

20
100100
5

21
100100
6

22
100100
7

23
100100
8

25
100100
9

27
100100
10

28
100100
11

31
100100
12

rb	tid	t_rank
13	100100	1
14	100100	2
15	100100	3
18	100100	4
20	100100	5
21	100100	6
22	100100	7
23	100100	8
25	100100	9
27	100100	10
28	100100	11
31	100100	12