On 2 Oct 2003 at 11:41, Harald Fuchs wrote about Re: Is something like this even possible? (Result > > I think I found a solution. For 4.1: > > SELECT r1.* > FROM mytbl r1, ( > SELECT x1.category, x1.itemid, COUNT(x2.category) AS rank > FROM mytbl x1 > LEFT JOIN mytbl x2 ON x2.category = x1.category > AND x2.itemid = x1.itemid > AND x2.timemodified < x1.timemodified > GROUP BY x1.category, x1.itemid > ) AS r2 > WHERE r2.category = r1.category > AND r2.itemid = r1.itemid > ORDER BY r1.category, r2.rank DESC, r1.timemodified DESC; > > For 4.0.x you'd need a temporary table instead of the subselect: > > CREATE TEMPORARY TABLE tmp AS > SELECT x1.category, x1.itemid, COUNT(x2.category) AS rank > FROM mytbl x1 > LEFT JOIN mytbl x2 ON x2.category = x1.category > AND x2.itemid = x1.itemid > AND x2.timemodified < x1.timemodified > GROUP BY x1.category, x1.itemid; > > SELECT r1.* > FROM mytbl r1 > JOIN tmp r2 ON r2.category = r1.category AND r2.itemid = r1.itemid > ORDER BY r1.category, r2.rank DESC, r1.timemodified DESC; >
That is close to what I was looking for thank you. I want to sort via the max timemodified for the group not the count, it is possible and item would be created later after the updates I listed in the example and I would want that at the top. Also, the tmp table was taking some 20+ seconds to complete and the result was taking about 6 so I modified the query as follows: CREATE TEMPORARY TABLE tmp AS SELECT x1.category, x1.itemid, max(x1.timemodified) AS rank FROM mytbl x1 GROUP BY x1.category, x1.itemid; SELECT r1.* FROM mytbl r1 JOIN tmp r2 ON r2.category = r1.category AND r2.itemid = r1.itemid ORDER BY r1.category, r2.rank DESC, r1.timemodified DESC; The complete query still takes about 10 seconds. with about 2 seconds for the tmp table and 8 for the results. Maybe I still need to rethink my design -Andrew -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
