Group by and order with joining tables

Question

Hi, I am having a problem sorting results from joining tables that have to be grouped in Mysql.

This is my tables setup.

Owners Table

• owner_id | owner_name
• 1 | Test owner 1
• 2 | Test owner 2
• 3 | Test owner 3

Images upload table

• image_id | image_name | ownerid | upload_date
• 1 | image1.jpg | 2 | 04-08-2009
• 2 | image2.jpg | 1 | 04-08-2009
• 3 | image3.jpg | 3 | 04-08-2009
• 4 | image4.jpg | 1 | 04-08-2009
• 5 | image5.jpg | 3 | 04-08-2009

The owner_id field is auto increment and so is the image_id fields.

What I am trying to do is get the owner_name for the last three uploaded images but not by the same owner. So in that example I would like it to return the following results.

Test owner 3 Test owner 1 Test owner 2

In that example the last owner to upload is Test owner 3 then Test owner 1 then Test owner 2.

I am using the following query but it does not return correct results

$sql = "SELECT u.*, s.* FROM UPLOAD_TBL u, OWNER_TBL s WHERE u.ownerid = s.owner_id
GROUP BY s.owner_id ORDER BY u.image_id DESC LIMIT 0, 3";

Any help setting up this query would be greatly appreciated.

Answer 1

You should simply group by owner_id and sort by upload_date DESC with LIMIT 3

Answer 2

How about something like:

select
     distinct(owner_id), owner_name
from
     owner
inner join
     images on images.ownerid = owner.ownerid
order by
     images.upload_date desc limit 3

Answer 3

See if you can sort on the aggregated image id:

select s.owner_id, s.owner_name, max(u.imag_id) as last_image_id
from UPLOAD_TBL u
inner join OWNER_TBL s on s.ownerid = u.owner_id
group by s.owner_id, s.owner_name
order by last_image_id desc
limit 3