Most efficient way to get lists?

Question

What's the best way to get a sublist of things?

I have two tables:

create table A (
  id int primary key
)

create table B (
  id int primary key,
  aid int foreign key references A( id ),
  sort_key int
)

I want to get a list of objects A, with subobjects B, but only the top five of B.

Let's say A is people, and B is type of food, with sort_key being how much a person likes that food. How do I get everybody (or some people) and their top 5 food?

Answer 1

This query assumes the sort key is one based, rather than zero:

  SELECT a.name
         b.food
    FROM A a
    JOIN B b ON b.aid = a.id
   WHERE b.sortkey <= 5
ORDER BY a.name, b.sortkey

Answer 2

On the previous comment if it's an INT you can't put non numerics in there.

Assuming the following data:

a
--
id
1
2
3

b
------------------------
id    aid   sort_key
1    1    1
2    1    2
3    2    1
4    3    1
5    1    3
6    1    4
7    1    5
8    1    6
9    2    2
10  2   3

The following query in MySQL would give you this:

SELECT a.*,
    (SELECT GROUP_CONCAT(id) AS ids FROM b AS bi WHERE bi.aid = a.id ORDER BY sort_key LIMIT 5) AS ids
FROM a

Result:

id   ids
1   1,2,5,6,7,8
2   3,9,10
3   4