I basically want to get the top sites (based on points) for each user. Below if the sample table I have prepared, having 2 users each having 2 sites each.
+----+-----+-----------+--------+ | id | uid | title | points | +----+-----+-----------+--------+ | 1 | 2 | Google | 20 | | 2 | 2 | Yahoo | 33 | | 3 | 3 | MSN | 11 | | 4 | 3 | AltaVista | 12 | +----+-----+-----------+--------+
The first thought that came to mind was:
SELECT id, uid, title, points from sites GROUP BY uid ORDER BY points DESC; +----+-----+--------+--------+ | id | uid | title | points | +----+-----+--------+--------+ | 1 | 2 | Google | 20 | | 3 | 3 | MSN | 11 | +----+-----+--------+--------+
The problem with this is we cannot set the order in which GROUP BY sorts the results (in mySQL 4.1 atleast). We did get the 2 sites for each user but not sorted the way we wanted (which was by points). Something like GROUP BY uid GROUPORDER BY points DESC would’ve been nice, unfortunately we don’t have this in mySQL.
Don’t bother trying using MAX() or DISTINCT(), since it won’t work. MAX(): will not correlate with the returned results, Selecting more than 1 row with DISTINCT will nullify DISTINCT()… which is stupid if you ask me.
So after half an hour on Google I finally stumbled upon the solution, which goes something like:
SELECT id,uid,title,points FROM ( SELECT id,uid,title,points FROM sites ORDER BY uid, /* artificial GROUP BY uid */ points DESC /* artificial MAX() of points */ ) tmpTable GROUP BY uid; /* select first row of each uid grouping */ +----+-----+-----------+--------+ | id | uid | title | points | +----+-----+-----------+--------+ | 2 | 2 | Yahoo | 33 | | 4 | 3 | AltaVista | 12 | +----+-----+-----------+--------+
