Hello, I have a table of items that change over time. These items are in a few categories. So my table looks something like the following simple version. +----------+--------+--------------+-------------+ | category | itemid | timemodified | currentdata | +----------+--------+--------------+-------------+ | c01 | i01 | 1 | created | | c01 | i02 | 2 | created | | c02 | i03 | 3 | created | | c02 | i04 | 4 | created | | c01 | i01 | 5 | update 1 | | c01 | i01 | 6 | update 2 | | c02 | i04 | 7 | update 1 | +----------+--------+--------------+-------------+
I would like to get a set results sorted like the following: +----------+--------+--------------+-------------+ | category | itemid | timemodified | currentdata | +----------+--------+--------------+-------------+ | c01 | i01 | 6 | update 2 | | c01 | i01 | 5 | update 1 | | c01 | i01 | 1 | created | | c01 | i02 | 2 | created | | c02 | i04 | 7 | update 1 | | c02 | i04 | 4 | created | | c02 | i03 | 3 | created | +----------+--------+--------------+-------------+ The road I was going down was 'order by category, Max(timemodified) desc, timemodified desc group by category, itemid' but that is wrong on several levels. Plus I don't want to sort via itemid directly, only via the max timemodified of the group. Let me word that different I want to sort first by category, then sort the groups of itemid (each sorted by timemodified) by the max of each group's timemodified. Currently I'm developing with version 4.0.14-log but I could upgrade to 4.1 if I needed to. I can get a list of the records of the max timemodified only, sorted by max timemodified for each group with something like the following, but this leaves out the historical records of the items, and I haven't been able to find how to include them in the output without doing many more queries, there are over 33k records in my real table and about 15k unique itemids. CREATE TEMPORARY TABLE tmp ( tmpitemid varchar(20), tmptimemodified INT(11)); LOCK TABLES testtable READ; INSERT INTO tmp (SELECT itemid, MAX(timemodified) FROM testtable GROUP BY itemid); SELECT testtable.* FROM testtable, tmp WHERE itemid = tmpitemid AND timemodified = tmptimemodified; UNLOCK TABLES; DROP TABLE tmp; If this isn't even possible then I'll go back and redesign my approach to this. Thank you, -Andrew -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
