SQL - Give me 3 hits for each type only

Question

I have some kind of impossible request :).

I have a table where one of the columns is named type. I would like to SELECT 3 records for each type in that column. Is that possible?

Note also that I'm using MySQL and Sphinx.

UPDATE: Table structure

id       title        type
1        AAAA         string1
2        CCCC         string2
3        EEEE         string2
4        DDDD         string2
5        FFFF         string2
6        BBBB         string2
6        BBBB         string2

What I want my MySQL to return is (up to 3 records for each type ordered by title):

id       title        type
1        AAAA         string1
6        BBBB         string2
2        CCCC         string2
4        DDDD         string2

Answer 1

select id, title, type
from   (select id, title, type,
               @num := if(@group = type, @num + 1, 1) as row_number,
               @group := type as dummy
        from   your_table
        order by type, title) as x
where  row_number <= 3

(Uses a different article on the same site as Martin Wickman's answer!)

Answer 2

If you have an index on (type, title), and you know the possible values for type, I believe that dynamic SQL is the way to go (for once) for best performance.

For each possible value of type, add a union all and a select for that specific type. The final query will look like the following query:

(select * from t1 where type = 'string1' order by title limit 3)
  union all
(select * from t1 where type = 'string2' order by title limit 3)
  union all
(select * from t1 where type = 'string3' order by title limit 3);

It executes in less than 1 second on a table with 1,000,000 rows, whereas the others solutions (Martins & Cyberkiwis) takes roughly 11 seconds.

The difference is because the unioned query above can fetch the first three title entries for each type and then stop, whereas the simulated analytics function has to scan the entire table.