Platform is mysql 3.x, so I have no subqueries available.
I have a one-table database containing a mixture of records -- news items, comments, folders, Web links, etc.
id int(11) NOT NULL auto_increment,
parent int(11),
title varchar(80) NOT NULL,
author varchar(80),
text longtext,
type tinyint(4),
pubdate timestamp(14),
... and so forth. Multiple comments are "attached" to an item by setting "parent" equal to the id of the parent. So, to get all of the comments related to item 5, I can do this:
SELECT * FROM item WHERE type=2 AND parent=5;
OK, so now here's what I want to do. I want to get the IDs of the six items that have spawned the most RECENT comments, AND the id of the most recent comment itself.
Easier said than done. I started out by selecting for discussions and referencing the "parent" field of the result set:
SELECT parent, id FROM item WHERE type=2 ORDER BY pubdate DESC LIMIT 6;
However, this gives me potential duplicate parents, since an item might have spawned two or three comments in the last couple of minutes. I want six unique parent IDs with the most recent timestamps. So I tried:
SELECT DISTINCT parent, id ....
... but the result set was not accurate; it appeared to be ignoring the ORDER BY clause. I tried GROUP BY parent with the same effect.
Eventually I worked around this by doing a SELECT with no DISTINCT keyword or LIMIT clause (grabbed the entire data set).
I then implemented the equivalent of DISTINCT in PHP, by shoving "parent" into an array and checking it on each iteration through a loop, and implemented LIMIT with a counter.
But it ought to be much faster to do this in SQL. So, the question is: What am I missing? Is there a way, given the limitations of mysql 3.x?
