[Edit - I've done something similar in a much nicer way in the followup post]
Hi :)
As I think you know, a GROUP BY member_id in MySQL will remove all but one row with the same member_id.
Are you trying to ensure that the one row that's left per member_id after the GROUP BY is the one with a image_main=1 (if there is one)?
This page
http://mysql.com/doc/refman/5.0/en/group-by-hidden-fields.html says "The server is free to return any value from the group, so the results are indeterminate unless all values are the same."
So I guess your ORDER BY won't help you the way you'd like it to.
So anyway I've had a very quick go at writing a query to do what you want - though it's probably not the most elegent way to do it.
It uses subqueries too, so you need to be using MySQL 4.1 or newer.
It's composed by running:
SELECT m.member_id as MMID, (select image_id from paz_images i where m.member_id=i.member_id order by img_main desc limit 1) as IIID FROM paz_members m;
which gives you a member_id and corresponding image_id for the highest img_main per member.
(this is where you add your second ORDER BY on img_insert_date if you want)
Once you have a member_id and corresponding image_id, it's just a matter of using these IDs to retrieve the rows you want from paz_image and paz_members
so, do something like:
SELECT m_o.*, i_o.*
FROM
paz_members m_o,
paz_images i_o,
(SELECT m.member_id AS MMID, (SELECT image_id FROM paz_images i WHERE m.member_id=i.member_id ORDER BY img_main DESC LIMIT 1) AS IIID FROM paz_members m) AS mi_i
WHERE
m_o.member_id = i_o.member_id
AND m_o.member_id = MMID
AND i_o.image_id = IIID;
It seems to work for me on a simple test table I made, though as I said I've not spent much time so it might be a bit buggy or slow or whatever.
It should give you a starting point to work from though. :)
Hope it helps,
Toasty
Edited 3 time(s). Last edit at 08/21/2006 02:19PM by Toa Sty.