2

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?

shiri
  • 115
  • 2
  • 12
  • 26 selects with union? (no there has to be a better way :0) –  Aug 09 '16 at 03:35
  • Hi thanks for the response. I tried your query but it still gives me all the words beginning with a,b,c etc (so instead of a max 26 words I'm getting 1987 words). I'm trying to get one word per letter. And yes there has to be a better way. – shiri Aug 09 '16 at 03:45
  • thats why i deleted, its distinct on 2 not one col... testing a few ideas locally at the mo –  Aug 09 '16 at 03:46
  • I found a related solution here: http://stackoverflow.com/questions/837058/returning-the-distinct-first-character-of-a-field-mysql – shiri Aug 09 '16 at 04:11

2 Answers2

2

How about group by the first letter of each word?

SELECT word_name FROM `word` GROUP BY SUBSTRING(word_name,0,1)
Jason
  • 326
  • 1
  • 12
2

this should work

SELECT DISTINCT LEFT(word_name, 1) as letter, word_name
FROM word
 GROUP BY (letter)
 ORDER BY letter
  • That indeed! So basically using distinct left was the right idea, and getting the first letter and using an alias which is then used to be grouped and ordered by. – shiri Aug 09 '16 at 04:04
  • Is there a way to get 2 words of each letter? – shiri Aug 09 '16 at 04:08
  • Ok by modifying the first query using "SELECT LEFT(word_name, 2)" this will return words with the first 2 characters that are unique. – shiri Aug 09 '16 at 04:17