SQL Give me 3 hits for each type.

Question

I have some impossible request :).

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

Notice also that I use 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 

I want my MySQL to be returned (up to 3 entries for each type sorted by name):

 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

When the table is large and the collection is more unpredictable, line numbering should be sorted by type in the internal query for processing side variables.

 select id, title, type from (select id, title, type, @r := CASE WHEN @g = type THEN @r+1 ELSE 1 END r, @g := type from tbl order by type, title) as x where row_number <= 3 # order by type, title 

Another way to do this without using side effects if the two entries do not match in type (name, type, identifier) ​​is given below. This uses only standard ANSI SQL92 SQL. This may be slower than indicated above.

 select A.id, A.title, A.type from tbl A left join tbl B on A.title = B.title and (A.type < B.type or (A.type = B.type and A.id < A.id)) group by A.id, A.title, A.type having count(B.title) <= 2 

Answer 3

If you have an index on (type, title) and you know the possible values ​​for type , I think dynamic SQL is the way to go (this time) for better performance.

For each possible type value, add the union of all and select for this particular type. The final request will look like this:

 (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 runs in less than 1 second on a table with 1,000,000 lines, while the rest of the solutions (Martins and Cyberkiwis) take about 11 seconds.

The difference is that the combined query above can retrieve the first three header records for each type and then stop , while the simulated analytic function should scan the entire table.

Answer 4

Check out this article. Given:

 +--------+------------+-------+ | type | variety | price | +--------+------------+-------+ | apple | gala | 2.79 | | apple | fuji | 0.24 | | apple | limbertwig | 2.87 | | orange | valencia | 3.59 | | orange | navel | 9.36 | | pear | bradford | 6.05 | | pear | bartlett | 2.14 | | cherry | bing | 2.55 | | cherry | chelan | 6.33 | +--------+------------+-------+ 

Query:

 select type, variety, price from fruits where ( select count(*) from fruits as f where f.type = fruits.type and f.price < fruits.price ) <= 2;