[Eikinskjaldi]

You can do this with a nested query

[sql]
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 albumid in (select max(albumid) from album group by userid)
ORDER BY $orderby DESC LIMIT 0, $tot
[/sql]


This assume the highest albumid for a user is the latest album.

This is probably quite slow, if this is a query you are running often then I would create a new latestalbum table and set up a trigger so that it updates when the album table updates.