mysql: sort before group

[jerx]

Thank you!

I think this won' t work. The album with the highest id is the latest album, but it does not necessarily hold the latest uploaded pictures of that user.

I tried your code anyway. First I ran into several error messages (table not found or subquery is ambiguous). In the end I had this:

  SELECT albumpicture.*, album.*, user.username, user.usergroupid,
  IF(displaygroupid=0, user.usergroupid, displaygroupid) AS displaygroupid 
  FROM " . TABLE_PREFIX . "albumpicture AS albumpicture
  LEFT JOIN " . TABLE_PREFIX . "album AS album ON(album.albumid = albumpicture.albumid)
  LEFT JOIN " . TABLE_PREFIX . "user AS user ON(user.userid = album.userid)
  WHERE album.state = 'public'
  AND album.albumid in (select max(album.albumid) from vb_album group by album.userid) 
  ORDER BY $orderby DESC LIMIT 0, $tot

It does not work. It gives me pictures of the same album. The result looks like the original code, but I think if you update an old album, this will give you wrong results.

But you suggest going another route anyway. This will be shown on the index page of the domain and therefore will be running very often. Unfortunetely I have no clue how to accomplish that. I was hoping that this change would be as easy as switching from dateline sort order to random display.

I just looked at vb 3.8 beta 1. It almost has a this feature. They created a new table for the album updates, too. But it only shows the coverpicture, not the latest picture added to that album. Nonetheless I would prefer this kind of display over displaying multiple pictures of the same albums.