i have this table architecture:

table user_media
user_id | media_id | date_media_added_ts

what i need to do is to select 10 newest media_id's for every user in
one query. there are only 10 users so no need to watch on the limit
there. it is maximum of 100 media_id's.
am i missing something very basic here?

thank you very much

On 8/18/11 3:30 PM, friglob wrote:
found the answer when googled the right way:

SELECT
media_id,user_id
FROM user_media
WHERE
(
SELECT count(*)
FROM user_media as R
WHERE R.user_id = user_media.user_id AND R.media_id > user_media.media_id
) <= 10
ORDER BY user_id ASC, date_added_ts DESC