select the first 5 records in a particular order for each foreign key

Question

I have a table with three columns

fk_id,
sort_column,
value_column

for each fk__id_ I'd like to retrieve the first 5 records order by the sort_column

I've tried this for several hours now and I don't have the slightest idea on how to do it

I'm using MySQL

I'd be grateful for any help

Marc

EDIT: I should probably clarify the output format I'm looking for: I want two columns, fk id and value_column fk id would just be repeated 5 times for each value

the reason I wanna do this is because later on I need to do some calculations on this results set such as calculating the average and the sum for each fk_id

Answer 1

I don't think this can be done. You would probably have to use a scripting language to do this individually for each fk_id

I could be wrong

Answer 2

Use this code and then add AND row_number < 5

Answer 3

This will do what you're looking for.

SELECT t1.fk_id, t1.value_column
FROM table AS t1
LEFT JOIN table AS t2
    ON t1.fk_id=t2.fk_id AND t1.sort_column < t2.sort_column
WHERE t2.fk_id IS NULL
ORDER BY t1.sort_column DESC
LIMIT 5