Mysql alphabetic query

Question

I need your help with the following question, not sure if it's possible:

I have a sql table like this:

|**id**  |**word**|
|---     |     ---|
|1       |   axxxx|
|2       |   a.....|
|.       |   a...|
|.       |   a....|
|.       |   a..|
|1000    |   a....|
|1001    |   b.....|
|1002    |   bxxxxx|
|.       |   b...|
|.       |   b..|
|.       |   .|
|60000   |   z.....|
|..      |   z..|

Basically, the field named 'word' contains words from A to Z and these has been already ordered alphabetical. So i need build a query which returns the first 3 rows by each letter, and example would be:

|**word**|
|---|
|ab|
|abc|
|abcd|
|bc|
|bcd|
|bcde|
|cd|
|cde|
|cdef|

I've tried several ways but didn't succeed, and at this point I'm not sure if that is really possible do it with a single query.

Thanks in advance, I'll really appreciate your advice and help.

Possible duplicate of [Using LIMIT within GROUP BY to get N results per group?](http://stackoverflow.com/questions/2129693/using-limit-within-group-by-to-get-n-results-per-group) — PM 77-1, Nov 01 '16 at 18:22

David דודו Markovitz · Answer 1 · 2016-11-01T19:28:13.350

Here is an example on the system table mysql.help_keyword.
In your case since your list contains only words (and not signs) you can skip the WHERE clause. Please notice that I'm counting on your words to be unique.

select      t1.name

from                mysql.help_keyword      as t1

            join    mysql.help_keyword      as t2

            on      left(t2.name,1) =  
                    left(t1.name,1)

                and t2.name         <= 
                    t1.name

where           left(t1.name,1) between 'A' and 'Z'
            and left(t2.name,1) between 'A' and 'Z'

group by        left(t1.name,1)
               ,t1.name

having          count(*) <= 3

order by        t1.name
;

score 0 · Answer 2 · answered Nov 01 '16 at 21:33

Thanks for your helping

I've already solved the question, with the follow query:

select word from words w,  ( select min(id) as ids from words group by left(word,1) ) as ids  where w.id between ids.ids and (ids.ids + 2);

It's a bit dirty, but works for me and the query is faster.

Mysql alphabetic query

2 Answers2