create table t1 (
pk int primary key,
a int,
b int
);
insert into t1 values
(11 , 0, 10),
(12 , 0, 10),
(13 , 1, 10),
(14 , 1, 10),
(18 , 2, 10),
(15 , 2, 20),
(16 , 2, 20),
(17 , 2, 20),
(19 , 4, 20),
(20 , 4, 20);
select pk, a, b, ntile(-1) over (order by a)
from t1;
ERROR HY000: Argument of NTILE must be greater than 0
select pk, a, b,
ntile(0) over (order by a)
from t1;
ERROR HY000: Argument of NTILE must be greater than 0
select pk, a, b,
ntile(1) over (order by pk)
from t1;
pk	a	b	ntile(1) over (order by pk)
11	0	10	1
12	0	10	1
13	1	10	1
14	1	10	1
15	2	20	1
16	2	20	1
17	2	20	1
18	2	10	1
19	4	20	1
20	4	20	1
select pk, a, b,
ntile(2) over (order by pk)
from t1;
pk	a	b	ntile(2) over (order by pk)
11	0	10	1
12	0	10	1
13	1	10	1
14	1	10	1
15	2	20	1
16	2	20	2
17	2	20	2
18	2	10	2
19	4	20	2
20	4	20	2
select pk, a, b,
ntile(3) over (order by pk)
from t1;
pk	a	b	ntile(3) over (order by pk)
11	0	10	1
12	0	10	1
13	1	10	1
14	1	10	1
15	2	20	2
16	2	20	2
17	2	20	2
18	2	10	3
19	4	20	3
20	4	20	3
select pk, a, b,
ntile(4) over (order by pk)
from t1;
pk	a	b	ntile(4) over (order by pk)
11	0	10	1
12	0	10	1
13	1	10	1
14	1	10	2
15	2	20	2
16	2	20	2
17	2	20	3
18	2	10	3
19	4	20	4
20	4	20	4
select pk, a, b,
ntile(5) over (order by pk)
from t1;
pk	a	b	ntile(5) over (order by pk)
11	0	10	1
12	0	10	1
13	1	10	2
14	1	10	2
15	2	20	3
16	2	20	3
17	2	20	4
18	2	10	4
19	4	20	5
20	4	20	5
select pk, a, b,
ntile(6) over (order by pk)
from t1;
pk	a	b	ntile(6) over (order by pk)
11	0	10	1
12	0	10	1
13	1	10	2
14	1	10	2
15	2	20	3
16	2	20	3
17	2	20	4
18	2	10	4
19	4	20	5
20	4	20	6
select pk, a, b,
ntile(7) over (order by pk)
from t1;
pk	a	b	ntile(7) over (order by pk)
11	0	10	1
12	0	10	1
13	1	10	2
14	1	10	2
15	2	20	3
16	2	20	3
17	2	20	4
18	2	10	5
19	4	20	6
20	4	20	7
select pk, a, b,
ntile(8) over (order by pk)
from t1;
pk	a	b	ntile(8) over (order by pk)
11	0	10	1
12	0	10	1
13	1	10	2
14	1	10	2
15	2	20	3
16	2	20	4
17	2	20	5
18	2	10	6
19	4	20	7
20	4	20	8
select pk, a, b,
ntile(9) over (order by pk)
from t1;
pk	a	b	ntile(9) over (order by pk)
11	0	10	1
12	0	10	1
13	1	10	2
14	1	10	3
15	2	20	4
16	2	20	5
17	2	20	6
18	2	10	7
19	4	20	8
20	4	20	9
select pk, a, b,
ntile(10) over (order by pk)
from t1;
pk	a	b	ntile(10) over (order by pk)
11	0	10	1
12	0	10	2
13	1	10	3
14	1	10	4
15	2	20	5
16	2	20	6
17	2	20	7
18	2	10	8
19	4	20	9
20	4	20	10
select pk, a, b,
ntile(11) over (order by pk)
from t1;
pk	a	b	ntile(11) over (order by pk)
11	0	10	1
12	0	10	2
13	1	10	3
14	1	10	4
15	2	20	5
16	2	20	6
17	2	20	7
18	2	10	8
19	4	20	9
20	4	20	10
select pk, a, b,
ntile(20) over (order by pk)
from t1;
pk	a	b	ntile(20) over (order by pk)
11	0	10	1
12	0	10	2
13	1	10	3
14	1	10	4
15	2	20	5
16	2	20	6
17	2	20	7
18	2	10	8
19	4	20	9
20	4	20	10
select pk, a, b,
ntile(1) over (partition by b order by pk)
from t1;
pk	a	b	ntile(1) over (partition by b order by pk)
11	0	10	1
12	0	10	1
13	1	10	1
14	1	10	1
18	2	10	1
15	2	20	1
16	2	20	1
17	2	20	1
19	4	20	1
20	4	20	1
select pk, a, b,
ntile(2) over (partition by b order by pk)
from t1;
pk	a	b	ntile(2) over (partition by b order by pk)
11	0	10	1
12	0	10	1
13	1	10	1
14	1	10	2
18	2	10	2
15	2	20	1
16	2	20	1
17	2	20	1
19	4	20	2
20	4	20	2
select pk, a, b,
ntile(3) over (partition by b order by pk)
from t1;
pk	a	b	ntile(3) over (partition by b order by pk)
11	0	10	1
12	0	10	1
13	1	10	2
14	1	10	2
18	2	10	3
15	2	20	1
16	2	20	1
17	2	20	2
19	4	20	2
20	4	20	3
select pk, a, b,
ntile(4) over (partition by b order by pk)
from t1;
pk	a	b	ntile(4) over (partition by b order by pk)
11	0	10	1
12	0	10	1
13	1	10	2
14	1	10	3
18	2	10	4
15	2	20	1
16	2	20	1
17	2	20	2
19	4	20	3
20	4	20	4
select pk, a, b,
ntile(5) over (partition by b order by pk)
from t1;
pk	a	b	ntile(5) over (partition by b order by pk)
11	0	10	1
12	0	10	2
13	1	10	3
14	1	10	4
18	2	10	5
15	2	20	1
16	2	20	2
17	2	20	3
19	4	20	4
20	4	20	5
select pk, a, b,
ntile(6) over (partition by b order by pk)
from t1;
pk	a	b	ntile(6) over (partition by b order by pk)
11	0	10	1
12	0	10	2
13	1	10	3
14	1	10	4
18	2	10	5
15	2	20	1
16	2	20	2
17	2	20	3
19	4	20	4
20	4	20	5
select pk, a, b,
ntile(7) over (partition by b order by pk)
from t1;
pk	a	b	ntile(7) over (partition by b order by pk)
11	0	10	1
12	0	10	2
13	1	10	3
14	1	10	4
18	2	10	5
15	2	20	1
16	2	20	2
17	2	20	3
19	4	20	4
20	4	20	5
select pk, a, b,
ntile(8) over (partition by b order by pk)
from t1;
pk	a	b	ntile(8) over (partition by b order by pk)
11	0	10	1
12	0	10	2
13	1	10	3
14	1	10	4
18	2	10	5
15	2	20	1
16	2	20	2
17	2	20	3
19	4	20	4
20	4	20	5
select pk, a, b,
ntile(9) over (partition by b order by pk)
from t1;
pk	a	b	ntile(9) over (partition by b order by pk)
11	0	10	1
12	0	10	2
13	1	10	3
14	1	10	4
18	2	10	5
15	2	20	1
16	2	20	2
17	2	20	3
19	4	20	4
20	4	20	5
select pk, a, b,
ntile(10) over (partition by b order by pk)
from t1;
pk	a	b	ntile(10) over (partition by b order by pk)
11	0	10	1
12	0	10	2
13	1	10	3
14	1	10	4
18	2	10	5
15	2	20	1
16	2	20	2
17	2	20	3
19	4	20	4
20	4	20	5
select pk, a, b,
ntile(11) over (partition by b order by pk)
from t1;
pk	a	b	ntile(11) over (partition by b order by pk)
11	0	10	1
12	0	10	2
13	1	10	3
14	1	10	4
18	2	10	5
15	2	20	1
16	2	20	2
17	2	20	3
19	4	20	4
20	4	20	5
select pk, a, b,
ntile(20) over (partition by b order by pk)
from t1;
pk	a	b	ntile(20) over (partition by b order by pk)
11	0	10	1
12	0	10	2
13	1	10	3
14	1	10	4
18	2	10	5
15	2	20	1
16	2	20	2
17	2	20	3
19	4	20	4
20	4	20	5
select pk, a, b,
ntile(1 + 3) over (partition by b order by pk)
from t1;
pk	a	b	ntile(1 + 3) over (partition by b order by pk)
11	0	10	1
12	0	10	1
13	1	10	2
14	1	10	3
18	2	10	4
15	2	20	1
16	2	20	1
17	2	20	2
19	4	20	3
20	4	20	4
select pk, a, b,
ntile((select 4)) over (partition by b order by pk)
from t1;
pk	a	b	ntile((select 4)) over (partition by b order by pk)
11	0	10	1
12	0	10	1
13	1	10	2
14	1	10	3
18	2	10	4
15	2	20	1
16	2	20	1
17	2	20	2
19	4	20	3
20	4	20	4
select t1.a from t1 where pk = 11;
a
0
select pk, a, b,
ntile((select a from t1 where pk=11)) over (partition by b order by pk)
from t1;
ERROR HY000: Argument of NTILE must be greater than 0
select t1.a from t1 where pk = 13;
a
1
select pk, a, b,
ntile((select a from t1 where pk=13)) over (partition by b order by pk)
from t1;
pk	a	b	ntile((select a from t1 where pk=13)) over (partition by b order by pk)
11	0	10	1
12	0	10	1
13	1	10	1
14	1	10	1
18	2	10	1
15	2	20	1
16	2	20	1
17	2	20	1
19	4	20	1
20	4	20	1
explain
select pk, a, b,
ntile((select a from t1 where pk=13)) over (partition by b order by pk)
from t1;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	10	Using temporary
2	SUBQUERY	t1	const	PRIMARY	PRIMARY	4	const	1	
select a from t1;
a
0
0
1
1
2
2
2
2
4
4
select pk, a, b,
ntile((select a from t1)) over (partition by b order by pk)
from t1;
ERROR 21000: Subquery returns more than 1 row
drop table t1;