select the first 5 records in a particular order for each foreign key
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
Use this code and then add AND row_number < 5
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
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
精彩评论