mySQL find most common starting letter for values

Question

From a mySQL table I would like to determine the most frequent starting letter; for example if the list is:

day
book
cat
dog
apple

The expected result would ultimately allow me to determine that:

'd' is the most frequent starting letter
'd' has a count of 2

Is there a way to do this without running 26 queries, e.g.:

WHERE myWord LIKE 'a%' 
WHERE myWord LIKE 'b%' 
...
WHERE myWord LIKE 'y%' 
WHERE myWord LIKE 'z%'

I found this SO question which makes me think I can do this in 2 steps:

If I'm not mistaken the approach would be to first build a list of all the first letters using the approach from this SO Answer something like this:

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

which I expect would look something like:

a
b
c
d
d

... and then query that list. To do this I would store that new list as a temporary table as per this SO question, something like:

CREATE TEMPORARY TABLE IF NOT EXISTS table2 AS (SELECT * FROM table1)

and query that for Magnitude as per this SO question, something like.

SELECT column, COUNT(*) AS magnitude 
FROM table 
GROUP BY column 
ORDER BY magnitude DESC
LIMIT 1

Is this a sensible approach?

NOTE:

As sometimes happens, in writing this question I think I figured out a way forward, as yet I have no working code. I'll update the question later with code that either works or which needs help.

In the meanwhile I appreciate any feedback, pointers, proposed answers.

Finally, I'm using PHP, PDO, mySQL for this.

TIA

For what it's worth there was an easier way, this is what I ended up with thanks to both who took the time to answer:

$stmt_common2 = $pdo->prepare('SELECT COUNT(*) as occurence,SUBSTRING(word,1,1) as letter
FROM words 
GROUP BY SUBSTRING(word,1,1)
ORDER BY  occurence DESC, letter ASC 
LIMIT 1');
$stmt_common2->execute();   
$mostCommon2 = $stmt_common2->fetchAll();       
echo "most common letter: " . $mostCommon2[0]['letter'] . " occurs " . $mostCommon2[0]['occurence'] . " times)<br>";

What if multiple letter comes equal times?? like `x and y` comes `n` times... — Yash Parekh, Jan 04 '18 at 07:12
@YashParekh - out of scope WRT my needs as I already know how to handle that situation, thanks — SlowLearner, Jan 04 '18 at 07:14
@Kirit thanks, will do - up voted both, just working through both - Thanks — SlowLearner, Jan 04 '18 at 07:40

Kirit · Accepted Answer · 2018-01-04T07:32:10.443

3

You can achieve by using this simple query

SELECT COUNT(*) as occurence,SUBSTRING(word_name,1,1) as letter
FROM word 
GROUP BY SUBSTRING(word_name,1,1)
ORDER BY  occurence DESC, letter ASC 
LIMIT 1

edited Jan 04 '18 at 07:32

answered Jan 04 '18 at 06:53

Kirit

405
3
18

Thank you :-) It was a tough choice between both answers, until one of them disappeared... Although I feel quite unqualified to accept one over the other I did find this easier to understand but both were of value. – SlowLearner Jan 04 '18 at 08:58

mySQL find most common starting letter for values

1 Answers1