MySQL 8.0

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

create table DemoTable
(
   Id int NOT NULL AUTO_INCREMENT PRIMARY KEY,
   Name varchar(20),
   State VARCHAR(20),
   Grade INT,
   Weight INT
);

INSERT INTO DemoTable(Name, State, Grade, Weight) VALUES ('John', 'NY', 100, 1);
INSERT INTO DemoTable(Name, State, Grade, Weight) VALUES ('Liam', 'NY', 90, 2);
INSERT INTO DemoTable(Name, State, Grade, Weight) VALUES ('Olivia', 'NY', 90, 3);
INSERT INTO DemoTable(Name, State, Grade, Weight) VALUES ('Emma', 'NY', 80, 4);
INSERT INTO DemoTable(Name, State, Grade, Weight) VALUES ('James', 'CA', 10, 1);
INSERT INTO DemoTable(Name, State, Grade, Weight) VALUES ('Henry', 'CA', 20, 1);
INSERT INTO DemoTable(Name, State, Grade, Weight) VALUES ('Mia', 'NJ', 50, 1);
INSERT INTO DemoTable(Name, State, Grade, Weight) VALUES ('Ava', 'NJ', 30, 4);

SELECT a.*,
(SELECT b.Grade FROM DemoTable b WHERE a.State = b.State 
ORDER BY RAND() * -b.Weight LIMIT 1) AS 'random_val' FROM DemoTable a;


Id
Name
State
Grade
Weight
random_val


1
John
NY
100
1
80

2
Liam
NY
90
2
90

3
Olivia
NY
90
3
90

4
Emma
NY
80
4
90

5
James
CA
10
1
10

6
Henry
CA
20
1
10

7
Mia
NJ
50
1
50

8
Ava
NJ
30
4
30


WITH
  ranged AS
(
  SELECT
    *,
    SUM(weight) OVER (PARTITION BY state ORDER BY id) - weight   AS weight_range_lower,
    SUM(weight) OVER (PARTITION BY state ORDER BY id)            AS weight_range_upper,
    SUM(weight) OVER (PARTITION BY state            ) * rand()   AS rand_threshold
  FROM
    DemoTable
)
SELECT
  ranged.*,
  lookup.grade   AS random_grade
FROM
  ranged
INNER JOIN
  ranged  AS lookup
    ON  lookup.state               = ranged.state
    AND lookup.weight_range_lower <= ranged.rand_threshold
    AND lookup.weight_range_upper >  ranged.rand_threshold
ORDER BY
  ranged.id


Id
Name
State
Grade
Weight
weight_range_lower
weight_range_upper
rand_threshold
random_grade


1
John
NY
100
1
0
1
2.858695269430704
90

2
Liam
NY
90
2
1
3
1.016128455303729
90

3
Olivia
NY
90
3
3
6
6.504556775562984
80

4
Emma
NY
80
4
6
10
9.474398819240182
80

5
James
CA
10
1
0
1
1.6115413174140651
20

6
Henry
CA
20
1
1
2
0.9571943608645297
10

7
Mia
NJ
50
1
0
1
4.878369783869357
30

8
Ava
NJ
30
4
1
5
2.2128913665310397
30


SELECT
  *,
  FIRST_VALUE(grade) OVER (PARTITION BY state ORDER BY weight * rand() DESC)
FROM
  DemoTable
ORDER BY
  id



Id
Name
State
Grade
Weight
FIRST_VALUE(grade) OVER (PARTITION BY state ORDER BY weight * rand() DESC)


1
John
NY
100
1
100

2
Liam
NY
90
2
100

3
Olivia
NY
90
3
100

4
Emma
NY
80
4
100

5
James
CA
10
1
20

6
Henry
CA
20
1
20

7
Mia
NJ
50
1
30

8
Ava
NJ
30
4
30

Id	Name	State	Grade	Weight	random_val
1	John	NY	100	1	80
2	Liam	NY	90	2	90
3	Olivia	NY	90	3	90
4	Emma	NY	80	4	90
5	James	CA	10	1	10
6	Henry	CA	20	1	10
7	Mia	NJ	50	1	50
8	Ava	NJ	30	4	30

Id	Name	State	Grade	Weight	weight_range_lower	weight_range_upper	rand_threshold	random_grade
1	John	NY	100	1	0	1	2.858695269430704	90
2	Liam	NY	90	2	1	3	1.016128455303729	90
3	Olivia	NY	90	3	3	6	6.504556775562984	80
4	Emma	NY	80	4	6	10	9.474398819240182	80
5	James	CA	10	1	0	1	1.6115413174140651	20
6	Henry	CA	20	1	1	2	0.9571943608645297	10
7	Mia	NJ	50	1	0	1	4.878369783869357	30
8	Ava	NJ	30	4	1	5	2.2128913665310397	30