Order by more than 1 field in INNER JOIN queries

I have the following SQL query:

SELECT products.id, 
       products.title, 
       products.size, 
       products.price, 
       products.text_description, 
       products.main_description, 
       products.priority, 
       gallery.id, 
       gallery.main 
       FROM gallery INNER JOIN products ON gallery.id=products.id 
       GROUP BY products.priority ASC

Each product in the database has several images associated with开发者_如何学Python it. The query above lists the product with 1 image of that product. Each picture has a field with a timestamp which the entry gets when uploading the image. I need to modify the SQL query above to get, say, the first image which was uploaded for the certain product, something like ORDER BY UPLOAD_TIME ASC. Could anybody help me with this?

Thank you.

Here's two ways to actually control which row you get in MySQL when you want the first row for a group

Using limit 1

SELECT products.id, 
       products.title, 
       products.size, 
       products.price, 
       products.text_description, 
       products.main_description, 
       products.priority, 
       gallery.id, 
       gallery.main 
FROM products
CROSS JOIN 
 (SELECT   
       gallery.id, 
       gallery.main  
    FROM 
        gallery
    WHERE
        products.[id] = gallery.[id]
    ORDER BY 
        gallery.[timestamp] DESC
    LIMIT 1) gallery
GROUP BY 
    products.priority ASC

Now this works as well. Using MAx()

SELECT products.id, 
       products.title, 
       products.size, 
       products.price, 
       products.text_description, 
       products.main_description, 
       products.priority, 
       gallery.id, 
       gallery.main 
FROM products
INNER JOIN gallery 
ON products.id = gallery.id
INNER JOIN 
   (SELECT   
       max(gallery.timestamp) timestamp, 
       gallery.[id]
    FROM 
        gallery
    GROUP BY
        gallery.[id]
    ) maxgallery
ON gallery.id = maxgallery.id and gallery.timestamp = maxgallery.timestamp
GROUP BY 
    products.priority ASC

Aside from potential differences in performance you must take special care on how these two queries handle ties

If there's two records that share id and timestamp using LIMIT 1 will only return one record. When using MAX() more than one record can be returned.

I'll leave it to you on which one is preferable or if it makes a difference at all.

INNER JOIN 
    (SELECT TOP 1 
         col1,
         col2,...,
         coln 
    FROM 
        products 
    WHERE
        products.[id] = gallery.[id]
    ORDER BY
        products.[timestamp] DESC)
ON...

This will be a slower query, as it needs to do the nested subquery for each row in the main query... perhaps loading the relevant table data into an in-memory table will speed it up.