hi here's my data structure:
categories
id name
----------------
1 dogs
2 cats
3 birds
images
id name cat datetime views
---------------------------------------
1 dog1 1 2015-10-01 123
2 dog2 1 2015-10-02 45
3 cat1 2 2015-10-03 678
4 cat2 2 2015-11-01 901
5 cat3 2 2015-11-02 234
6 bird1 3 2015-12-03 456
i want to output: all categories and each category should show:
desired output:
cat name images views latest id_latest
----------------------------------------------------
1 dogs 2 123 2015-10-02 5
2 cats 3 901 2015-11-02 4
3 birds 1 456 2015-12-03 6
my query so far:
Select
categories.*,
Count(images.id) As images,
Sum(images.views) As views,
Max(images.datetime) As latest
From
categories
Left Join images On images.catid = categories.id
Group By
categories.id
the problem is: how would i get the last field id_latest?
thanks
categories
catid name
----------------
1 dogs
2 cats
3 birds
images
imgid name catid datetime views
---------------------------------------
1 dog1 1 2015-10-01 123
2 dog2 1 2015-10-02 45
3 cat1 2 2015-10-03 678
4 cat2 2 2015-11-01 901
5 cat3 2 2015-11-02 234
6 bird1 3 2015-12-03 456
And code :
Select
categories.catid,
categories.name,
Count(images.imgid) As images,
Sum(images.views) As views,
Max(images.datetime) As latest
From
categories
Left Join images On images.catid = categories.catid
Group By
categories.catid, categories.name
Select t.*, i.id as imageID
from
(Select
categories.*,
Count(images.id) As images,
Sum(images.views) As views,
Max(images.datetime) As latest
From categories
Left Join
images
On images.catid = categories.id
Group By categories.id
) t
left join
images i
on i.`datetime`= latest