Forum Moderators: open
I know this is probably a stupid question. I am trying to bring up a list of the last 5 photos posted on a website. These have subcategories, so I want to have a dynamic table that always shows the last 5 topics that were posted in. So I want the categories to be distinct, I need the table to be sorted in descended order but I also need the id of each record so I can send that as a parameter to the dynamic page when they click on the link. Since each id is unique, I can't use the DISTINCT code. When I try GROUP BY, it doesn't come out in the right order and I can't determine what the order is and why. Every variation that I have tried, does not bring up the last 5 distinct posting and I can't figure out why. Basically, I have the picture table where I pull the category id (numerical) which is linked to a table that gives the name of the category. This is the query:
SELECT gen_pics.event_name AS event_id, gen_pics.id, event_list_pic.event_name AS ev_name
FROM (gen_pics LEFT JOIN event_list_pic ON event_list_pic.id=gen_pics.event_name)
WHERE event_list_pic.galleryVisible=1
GROUP BY event_id
ORDER BY gen_pics.id DESC
I'm not sure what I'm doing wrong.
Thanks.
If I understand your problem, it sounds like you need a correlated subquery to select the most recent pic for each distinct category. (Not sure you needed the outer join, since this is how you distinguish the categories.)
Like this:
select gen_pics.event_name as event_id,gen_pics.id,event_list_pic.event_name as ev_name
from gen_pics,event_list_pic
where gen_pics.event_name=event_list_pic.id
and event_list_pic.galleryvisible=1
and gen_pics.id = (select max(g2.id)
from gen_pics g2
where g2.event_name = gen_pics.event_name)
order by gen_pics.id desc
limit 5
This works brilliantly! I'm sorry I haven't posted sooner but I thought I would get notification of a reply and just thought no one had replied.
I'm not exactly sure what the g2 coding is doing. I'd love to understand it since it's exactly what I wanted! Could you explain what that is?
Thanks SO MUCH!
(select max(g2.id)
from gen_pics g2
where g2.event_name = gen_pics.event_name)
