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 (9754 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)


SELECT the_word, no_of_times FROM test ORDER BY no_of_times DESC, RAND() LIMIT 15;
the_word no_of_times
keyword1 100
keyword2 90
keyword3 80
keyword4 70
keyword5 60
keyword6 50
keyword8 40
keyword22 40
keyword15 40
keyword17 40
keyword18 40
keyword7 40
keyword12 40
keyword10 40
keyword21 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;
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 'cte1 AS ( SELECT 15 AS tot_num_recs_wanted -- the 15 could come from a table ' at line 1
 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;
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 'cte1 AS ( SELECT 15 AS tot_num_recs_wanted -- the 15 could come from a table ' at line 1
 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;
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 'cte1 AS ( SELECT 15 AS tot_num_recs_wanted -- the 15 could come from a table ' at line 1
 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;
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 'cte1 AS ( SELECT 15 AS tot_num_recs_wanted -- the 15 could come from a table ' at line 1
 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;
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 'cte1 AS ( SELECT 15 AS tot_num_recs_wanted -- the 15 could come from a table ' at line 1
 hidden batch(es)