Ordering the result of a left join

Question

This is the code i have: The 'media_work' table has an 'order' field that indicate the order of the files. I want to sort the result of the 'media_work' table to get the first item (image)

This code works but doesn't order the 'media_work'

SELECT work.id, work.title, media.file_name_thumb,media_work.order, 
COUNT(media.file_name_thumb) AS mycount
FROM work
LEFT OUTER JOIN media_work ON media_work.work_id = work.id
LEFT OUTER JOIN media ON media_work.media_id = media.id
GROUP BY work.id ORDER BY work.id DESC

This is the result

id   title          file_name_thumb                 order   mycount
-------------------------------------------------------------------
3    laatste        thumb_20101028134738_278.gif    1       3
2    test work add  thumb_20101028133811_893.gif    1       56
1    test werk      thumb_20101028132316_537.jpg    2       5

I want the order to be ordered Ascending so it'll be '0'

Answer 1

If you want to select first media for each work:

SELECT  work.id, work.title, media.file_name_thumb,
        (
        SELECT  COUNT(*)
        FROM    media_work
        WHERE   media_work.work_id = work.id
        )
FROM    work
LEFT JOIN
        media
ON      media.id =
        (
        SELECT  media_work.media_id
        FROM    media_work
        WHERE   media_work.work_id = work.id
        ORDER BY
                media_work.work_id, media_work.order
        LIMIT 1
        )

Create an index on media_work (work_id, order) for this to work fast.