clear markdown compare help donate or buy diddy dollings comments/suggestions/bugs a leap of faith? bible365
clear markdown donate comments/suggestions/bugs a leap of faith? bible365 diddy dollings
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0. 809174 fiddles created (9757 in the last week).

CREATE TABLE test -- 22 records ( the_word TEXT, no_of_times INTEGER ); INSERT INTO test VALUES ('keyword1', 100); INSERT INTO test VALUES ('keyword2', 90); INSERT INTO test VALUES ('keyword3', 80); INSERT INTO test VALUES ('keyword4', 70); INSERT INTO test VALUES ('keyword5', 60); INSERT INTO test VALUES ('keyword6', 50); INSERT INTO test VALUES ('keyword7', 40); INSERT INTO test VALUES ('keyword8', 40); INSERT INTO test VALUES ('keyword9', 40); INSERT INTO test VALUES ('keyword10', 40); INSERT INTO test VALUES ('keyword11', 40); INSERT INTO test VALUES ('keyword12', 40); INSERT INTO test VALUES ('keyword13', 40); INSERT INTO test VALUES ('keyword14', 40); INSERT INTO test VALUES ('keyword15', 40); INSERT INTO test VALUES ('keyword16', 40); INSERT INTO test VALUES ('keyword17', 40); INSERT INTO test VALUES ('keyword18', 40); INSERT INTO test VALUES ('keyword19', 40); INSERT INTO test VALUES ('keyword20', 40); INSERT INTO test VALUES ('keyword21', 40); INSERT INTO test VALUES ('keyword22', 40);
 hidden batch(es)


WITH cte1 AS ( SELECT 15 AS tot_num_recs_wanted -- the 15 could come from a table ), cte2 AS ( SELECT *, ROW_NUMBER() OVER (ORDER BY no_of_times DESC) AS rn FROM test ) SELECT * FROM cte2;
the_word no_of_times rn
keyword1 100 1
keyword2 90 2
keyword3 80 3
keyword4 70 4
keyword5 60 5
keyword6 50 6
keyword7 40 7
keyword8 40 8
keyword9 40 9
keyword10 40 10
keyword11 40 11
keyword12 40 12
keyword13 40 13
keyword14 40 14
keyword15 40 15
keyword16 40 16
keyword17 40 17
keyword18 40 18
keyword19 40 19
keyword20 40 20
keyword21 40 21
keyword22 40 22
 hidden batch(es)


WITH cte1 AS ( SELECT 15 AS tot_num_recs_wanted -- the 15 could come from a table ), cte2 AS ( SELECT *, ROW_NUMBER() OVER (ORDER BY no_of_times DESC) AS rn FROM test -- putting LIMIT 1 OFFSET (SELECT tot_num_recs_wanted -- FROM cte1) here would have made life easier - it would -- have eliminated the need for cte3 and the ROW_NUMBER() construct ), cte3 AS ( SELECT rn, no_of_times nt FROM cte2 WHERE rn = (SELECT tot_num_recs_wanted FROM cte1) ) SELECT * FROM cte3;
rn nt
15 40
 hidden batch(es)


WITH cte1 AS ( SELECT 15 AS tot_num_recs_wanted -- the 15 could come from a table ), cte2 AS ( SELECT *, ROW_NUMBER() OVER (ORDER BY no_of_times DESC) AS rn FROM test ), cte3 AS ( SELECT rn, no_of_times nt FROM cte2 WHERE rn = (SELECT tot_num_recs_wanted FROM cte1) ), cte4 AS ( SELECT *, ROW_NUMBER() OVER (ORDER BY RAND()) AS rand_rn FROM test WHERE no_of_times = (SELECT nt FROM cte3) -- i.e. 40 ) SELECT * FROM cte4;
the_word no_of_times rand_rn
keyword8 40 1
keyword19 40 2
keyword11 40 3
keyword21 40 4
keyword18 40 5
keyword10 40 6
keyword17 40 7
keyword9 40 8
keyword16 40 9
keyword12 40 10
keyword20 40 11
keyword13 40 12
keyword15 40 13
keyword14 40 14
keyword7 40 15
keyword22 40 16
 hidden batch(es)


WITH cte1 AS ( SELECT 15 AS tot_num_recs_wanted -- the 15 could come from a table ), cte2 AS ( SELECT *, ROW_NUMBER() OVER (ORDER BY no_of_times DESC) AS rn FROM test ), cte3 AS ( SELECT rn, no_of_times nt FROM cte2 WHERE rn = (SELECT tot_num_recs_wanted FROM cte1) ), cte4 AS ( SELECT *, ROW_NUMBER() OVER (ORDER BY RAND()) AS rand_rn FROM test WHERE no_of_times = (SELECT nt FROM cte3) ), cte5 AS ( SELECT * FROM cte4 WHERE rand_rn <= (SELECT tot_num_recs_wanted FROM cte1) - (SELECT COUNT(*) FROM test WHERE no_of_times > (SELECT nt FROM cte3)) ) SELECT * FROM cte5;
the_word no_of_times rand_rn
keyword15 40 1
keyword18 40 2
keyword22 40 3
keyword10 40 4
keyword7 40 5
keyword21 40 6
keyword14 40 7
keyword16 40 8
keyword12 40 9
 hidden batch(es)


WITH cte1 AS ( SELECT 15 AS tot_num_recs_wanted -- the 15 could come from a table ), cte2 AS ( SELECT *, ROW_NUMBER() OVER (ORDER BY no_of_times DESC) AS rn FROM test ), cte3 AS ( SELECT rn, no_of_times nt FROM cte2 WHERE rn = (SELECT tot_num_recs_wanted FROM cte1) ), cte4 AS ( SELECT *, ROW_NUMBER() OVER (ORDER BY RAND()) AS rand_rn FROM test WHERE no_of_times = (SELECT nt FROM cte3) ), cte5 AS ( SELECT * FROM cte4 WHERE rand_rn <= (SELECT tot_num_recs_wanted FROM cte1) - (SELECT COUNT(*) FROM test WHERE no_of_times > (SELECT nt FROM cte3)) ) SELECT the_word, no_of_times FROM cte5 UNION SELECT the_word, no_of_times FROM test WHERE no_of_times > (SELECT nt FROM cte3) ORDER BY no_of_times DESC;
the_word no_of_times
keyword1 100
keyword2 90
keyword3 80
keyword4 70
keyword5 60
keyword6 50
keyword19 40
keyword16 40
keyword20 40
keyword17 40
keyword18 40
keyword9 40
keyword15 40
keyword12 40
keyword8 40
 hidden batch(es)