I am having trouble selecting best rated images in a gallery that is categorized. The project wants to be an image library with categorized images.
What i would like to do is to design the tables, and the selection query, in such way that i will be able to execute one query and obtain Best performing image from every category, something like the image that is best rated in its category. All this within one query.
The result should show the list of my predefined categories with its corresponding image that is best rated in its category at the moment.
I know i can do this with for loops in php and issue a query for every category, but that will not be a optimized way.
The table design is still a work in progress but if someone can point me to the basic idea of the select format that would work on my test tables would be rewarded.
PS : I have tried a lot of select variations combined with GROUP BY but still no success.
Also in my design i use a table for categories and one for the actual thumbnails.