CODEWITHSUNDEEP
mysql
SlowLearner
SlowLearner

Reputation: 3294

mySQL find most common starting letter for values

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

The expected result would ultimately allow me to determine that:

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:

... 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>";

Upvotes: 0

Views: 1000

Answers (1)

Kirit
Kirit

Reputation: 415

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

Upvotes: 3

Related Questions