I have a fairly complex query, which is for a click exchange (where users can use their x.credits for clicks on their exchange_links l). This query is what generates a list of click links for a user to click on. Through the various WHERE conditions, it ensures that: (1) they don't see their own link (can't click your own!), (2) they haven't clicked the link in the exchange already (AND l.id NOT IN), and (3) they haven't clicked the link OUTSIDE of the exchange either (AND l.eggid NOT IN).
The troublesome part is the GROUP BY x.user_id. I only want to display one link per user. The issue is that when I use this method, the GROUP BY clause will always return on ORDER BY l.id ASC. But I want to return a random record from each GROUP BY set to ensure that click links are evenly distributed. I've done some reading on this to grasp a general concept of how to do this, but I'm at a loss as to how to write it in this query with the JOINs everywhere.
Thanks in advanced.
FROM exchange_links l
INNER JOIN eggs e
ON l.egg_id = e.id
INNER JOIN users u
ON e.user_id = u.id
INNER JOIN exchange x
ON u.id = x.user_id
WHERE u.id != '.$this->db->escape($this->sess->member('id')).'
AND l.id NOT IN ( SELECT c.link_id
FROM exchange_clicks c
INNER JOIN exchange_links l
ON c.link_id = l.id
WHERE c.ip = '.$this->db->escape($this->input->ip_address()).'
AND l.egg_id = e.id
AND l.egg_id NOT IN ( SELECT eggid
WHERE ip = '.$this->db->escape($this->input->ip_address()).'
AND x.credits > 0
GROUP BY x.user_id
ORDER BY x.credits DESC, l.time