phpqa

How to select a specified number of values starting with each alphabets in mysql.

Suppose you have a table named 'names', which has fields id and name, and you need to select two or three names from each alphabet and display it in a page. For example you have to select 3 names from the table, which contains the names starting with all the alphabets from a-z. ie, You will need to display 26 * 3 = 72 names.

Here is the answer,

SELECT 
 letter, 
 name 
FROM (
 SELECT 
  LEFT( name, 1 ) AS letter, 
  name, 
  @num := IF( @prev = LEFT( name, 1 ),@num +1, 1 ) AS row_num, 
  @prev := LEFT( name, 1 ) AS previous
 FROM 
  names,(
   SELECT 
    @num  :=0, 
    @prev := ''
  )  PHPQA
 ORDER BY name
 ) QAPHP
WHERE row_num <=3

You can find its live example on following page.
 http://sqlfiddle.com/#!2/abfd8/3
http://sqlfiddle.com/#!2/4887b/1