Pick 3 random words (entries) - one from each number of letters
I have a table with a lot of words, as well as a field that contains the number of letters for each word.
EDIT: The sample data below only shows the layout of the table - there are 65,000 rows of all length combinations.
word_id,word,letter_count 54908,swanage,7 37338,natured,7 61637,untwisting,10
I am trying to pick three random words, making sure there is one that has 4 letters, of which 7 are letters, and the number is 10.
My SQL query is below.
SELECT * FROM password
WHERE letter_count = 4 OR letter_count = 7 OR letter_count = 10
ORDER BY RAND()
LIMIT 3
The above will return 3 random entries, but can contain any combination of 4, 7, or 10 letters.
Can anyone please help?
Thank,
John
+3
source to share
1 answer
DROP TABLE IF EXISTS my_table;
CREATE TABLE my_table
(word_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY
,word VARCHAR(20) NOT NULL UNIQUE
);
INSERT INTO my_table VALUES
(1,'swanage'),
(2,'natured'),
(3,'untwisting'),
(4,'four'),
(5,'five'),
(6,'fifteen'),
(7,'blacksmith');
SELECT *
FROM
( SELECT * FROM my_table WHERE CHAR_LENGTH(word) = 4 ORDER BY RAND() LIMIT 1 ) x
UNION
( SELECT * FROM my_table WHERE CHAR_LENGTH(word) = 7 ORDER BY RAND() LIMIT 1 )
UNION
( SELECT * FROM my_table WHERE CHAR_LENGTH(word) = 10 ORDER BY RAND() LIMIT 1) ;
+---------+------------+
| word_id | word |
+---------+------------+
| 4 | four |
| 1 | swanage |
| 3 | untwisting |
+---------+------------+
+2
source to share