Sorting by a to z with Limit

Question

I have one table customers which has one field 'name' with a to z names records.

I get records from a to z with asc query

SELECT * FROM `customers` ORDER BY name ASC

But how can i get 5 records which starts with all a to z alphabets with only one query?

Output:

1. a

2. a

3. a

4. a

5. a

6. b

7. b

8. b

9. b

10. b and so on to z. Thanks in advance.

Answer 1

Try this:

SELECT c.name
FROM (SELECT c.name, IF(@lastLetter=@lastLetter:=LEFT(c.name, 1), @cnt:=@cnt+1, @cnt:=0) letterCnt
      FROM customers c, (SELECT @lastLetter:='', @cnt:=0) A 
      ORDER BY c.name ASC
    ) AS c
WHERE c.letterCnt < 5