Postgres 9.6

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

CREATE TABLE pro_list (
    pro_id int,
    pro_name text,
    pro_cat_id int
);

INSERT INTO pro_list VALUES
(1, 'AA', 1), -- category with more then <limit> products
(2, 'AB', 1),
(3, 'AC', 1),
(4, 'AD', 1),
(5, 'AE', 1),
(6, 'AF', 1),
(7, 'AG', 1),
(8, 'AH', 1),
(9, 'AI', 1),
(10, 'AJ', 1),
(11, 'BA', 2), -- category with less then <limit> products
(12, 'BB', 2),
(13, 'BC', 2),
(14, 'CA', 3), -- unchosen category
(15, 'CB', 3),
(16, 'CC', 3),
(17, 'CD', 3),
(18, 'CE', 3),
(19, 'CF', 3),
(20, 'CG', 3),
(21, 'DA', 4), -- category with exact <limit> products
(22, 'DB', 4),
(23, 'DC', 4),
(24, 'DD', 4),
(25, 'DE', 4),
(26, 'EA', 5), -- unchosen category
(27, 'EB', 5),
(28, 'EC', 5),
(29, 'ED', 5),
(30, 'EE', 5),
(31, 'EF', 5);

SELECT * FROM pro_list;
31 rows affected



pro_id
pro_name
pro_cat_id


1
AA
1

2
AB
1

3
AC
1

4
AD
1

5
AE
1

6
AF
1

7
AG
1

8
AH
1

9
AI
1

10
AJ
1

11
BA
2

12
BB
2

13
BC
2

14
CA
3

15
CB
3

16
CC
3

17
CD
3

18
CE
3

19
CF
3

20
CG
3

21
DA
4

22
DB
4

23
DC
4

24
DD
4

25
DE
4

26
EA
5

27
EB
5

28
EC
5

29
ED
5

30
EE
5

31
EF
5


-- demo with limit == 5 and chosen categories 1, 2, 4
-- demo for category list

SELECT pro_id, pro_name, pro_cat_id
FROM (
    SELECT 
        pro_id, 
        pro_name, 
        pro_cat_id, 
        row_number() OVER (PARTITION BY pro_cat_id) 
    FROM pro_list
    WHERE pro_cat_id IN (1, 2, 4)
)s
WHERE row_number <= 5


pro_id
pro_name
pro_cat_id


1
AA
1

2
AB
1

3
AC
1

4
AD
1

5
AE
1

11
BA
2

12
BB
2

13
BC
2

21
DA
4

22
DB
4

23
DC
4

24
DD
4

25
DE
4


-- demo for category array

SELECT pro_id, pro_name, pro_cat_id
FROM (
    SELECT 
        pro_id, 
        pro_name, 
        pro_cat_id, 
        row_number() OVER (PARTITION BY pro_cat_id) 
    FROM pro_list
    WHERE pro_cat_id = ANY(ARRAY[1, 2, 4])
)s
WHERE row_number <= 5


pro_id
pro_name
pro_cat_id


1
AA
1

2
AB
1

3
AC
1

4
AD
1

5
AE
1

11
BA
2

12
BB
2

13
BC
2

21
DA
4

22
DB
4

23
DC
4

24
DD
4

25
DE
4

pro_id	pro_name	pro_cat_id
1	AA	1
2	AB	1
3	AC	1
4	AD	1
5	AE	1
6	AF	1
7	AG	1
8	AH	1
9	AI	1
10	AJ	1
11	BA	2
12	BB	2
13	BC	2
14	CA	3
15	CB	3
16	CC	3
17	CD	3
18	CE	3
19	CF	3
20	CG	3
21	DA	4
22	DB	4
23	DC	4
24	DD	4
25	DE	4
26	EA	5
27	EB	5
28	EC	5
29	ED	5
30	EE	5
31	EF	5

pro_id	pro_name	pro_cat_id
1	AA	1
2	AB	1
3	AC	1
4	AD	1
5	AE	1
11	BA	2
12	BB	2
13	BC	2
21	DA	4
22	DB	4
23	DC	4
24	DD	4
25	DE	4

pro_id	pro_name	pro_cat_id
1	AA	1
2	AB	1
3	AC	1
4	AD	1
5	AE	1
11	BA	2
12	BB	2
13	BC	2
21	DA	4
22	DB	4
23	DC	4
24	DD	4
25	DE	4

pro_id	pro_name	pro_cat_id
1	AA	1
2	AB	1
3	AC	1
4	AD	1
5	AE	1
6	AF	1
7	AG	1
8	AH	1
9	AI	1
10	AJ	1
11	BA	2
12	BB	2
13	BC	2
14	CA	3
15	CB	3
16	CC	3
17	CD	3
18	CE	3
19	CF	3
20	CG	3
21	DA	4
22	DB	4
23	DC	4
24	DD	4
25	DE	4
26	EA	5
27	EB	5
28	EC	5
29	ED	5
30	EE	5
31	EF	5

pro_id	pro_name	pro_cat_id
1	AA	1
2	AB	1
3	AC	1
4	AD	1
5	AE	1
11	BA	2
12	BB	2
13	BC	2
21	DA	4
22	DB	4
23	DC	4
24	DD	4
25	DE	4

pro_id	pro_name	pro_cat_id
1	AA	1
2	AB	1
3	AC	1
4	AD	1
5	AE	1
11	BA	2
12	BB	2
13	BC	2
21	DA	4
22	DB	4
23	DC	4
24	DD	4
25	DE	4

pro_id	pro_name	pro_cat_id
1	AA	1
2	AB	1
3	AC	1
4	AD	1
5	AE	1
6	AF	1
7	AG	1
8	AH	1
9	AI	1
10	AJ	1
11	BA	2
12	BB	2
13	BC	2
14	CA	3
15	CB	3
16	CC	3
17	CD	3
18	CE	3
19	CF	3
20	CG	3
21	DA	4
22	DB	4
23	DC	4
24	DD	4
25	DE	4
26	EA	5
27	EB	5
28	EC	5
29	ED	5
30	EE	5
31	EF	5

pro_id	pro_name	pro_cat_id
1	AA	1
2	AB	1
3	AC	1
4	AD	1
5	AE	1
11	BA	2
12	BB	2
13	BC	2
21	DA	4
22	DB	4
23	DC	4
24	DD	4
25	DE	4

pro_id	pro_name	pro_cat_id
1	AA	1
2	AB	1
3	AC	1
4	AD	1
5	AE	1
11	BA	2
12	BB	2
13	BC	2
21	DA	4
22	DB	4
23	DC	4
24	DD	4
25	DE	4