SQL Complicated Group / Join by Category

Question

I currently have a database structure with two important tables.

1) Food Types (Fruit, Vegetables, Meat)

2) Specific Foods (Apple, Oranges, Carrots, Lettuce, Steak, Pork)

I am currently trying to build a SQL statement such that I can have the following.

Fruit < Apple, Orange

Vegetables < Carrots, Lettuce

Meat < Steak, Port

I have tried using a statement like the following

Select * From Food_Type join (Select * From Foods) as Foods on Food_Type.Type_ID = Foods.Type_ID

but this returns every Specific Food, while I only want the first 2 per category. So I basically need my subquery to have a limit statement in it so that it finds only the first 2 per category.

However if I simply do the following

   Select * From Food_Type join (Select * From Foods LIMIT 2) as Foods on Food_Type.Type_ID = Foods.Type_ID

My statement only returns 2 results total.

Answer 1

Answered here, but that answer was mysql specific in the end.

If your database is a more compliant to the standard you can also use PARTITION

Note:

Select * 
From Food_Type join 
     (Select * From Foods LIMIT 2) as Foods on Food_Type.Type_ID = Foods.Type_ID

(Select * From Foods LIMIT 2) returns only two records out of the whole table, the join happens only after the query is returned.

To do what you are thinking about you will have to use nested subqueries.

Select * 
From Food_Type join 
     Foods on Food_Type.Type_ID = Foods.Type_ID
Where Foods.Foods_ID IN (Select * 
                         From Foods AS nested 
                         Where nested.Type_ID = Food_Type.Type_ID 
                         ORDER BY ? 
                         LIMIT 2)

PARTITION/RANK approach is different and should be faster, if you need help let us know.