CODEWITHSUNDEEP
phpmysql
shiri
shiri

Reputation: 115

Mysql - How to search for 26 records that each begins with the letter of the alphabet?

Basically I'm trying to create a query that can retrieve 26 words from a table based on the letters of the English alphabet (26 letters). So "Apple, Banana, Coconut..." etc.

I've been using 'like a%', so:

SELECT * from 'word' WHERE word_name like 'a%' limit 1 - which gives me a word that begins with 'a'.

SELECT * from 'word' WHERE word_name like 'a%' or word_name like 'b%' - which gives me multiple words beginning with 'a' or 'b'.

SELECT * FROMwordWHERE word_name BETWEEN 'a' AND 'e' - list all words that begins with a-e. Seems more powerful. From here, I need to know how to limit the query to finding just one word per letter and 26 words in total, I've tried the following:

SELECT DISTINCT word_name FROM `word` WHERE word_name BETWEEN 'a' AND 'z' limit 26;

SELECT DISTINCT word_name FROM `word` WHERE word_name like [a-z]% limit 26;

I feel like I'm getting the right idea but neither of these are working for me. I'm familiar with php so I could go the really long route and create a query for every letter and put that into an array, but is there already a mysql solution for this?

Upvotes: 2

Views: 137

Answers (2)

user557846
user557846

Reputation:

this should work

SELECT DISTINCT LEFT(word_name, 1) as letter, word_name
FROM word
 GROUP BY (letter)
 ORDER BY letter

Upvotes: 2

Jason
Jason

Reputation: 336

How about group by the first letter of each word?

SELECT word_name FROM `word` GROUP BY SUBSTRING(word_name,0,1)

Upvotes: 2

Related Questions