So the first task is to find the OWNER of each
itemid.
This is easy enough:
Code:
SELECT C.itemid, C.userid
FROM comments AS C,
( SELECT itemid, MIN(dateAdded) AS firstpost FROM comments GROUP BY itemid ) AS M
WHERE C.itemid = M.itemid
AND C.dateAdded = M.firstpost;
Which gives us:
Code:
+--------+--------+
| itemid | userid |
+--------+--------+
| 383 | ABC3EF |
| 163 | ABC3EF |
| 3ef | 100 |
+--------+--------+
You might even want to turn that into a pair of views:
Code:
mysql> CREATE VIEW firstPosts AS
-> SELECT itemid, MIN(dateAdded) AS firstpost FROM comments GROUP BY itemid;
Query OK, 0 rows affected (0.00 sec)
mysql> CREATE VIEW getItemOwners AS
-> SELECT C.itemid, C.userid
-> FROM comments AS C, firstPosts AS F
-> WHERE C.itemid = F.itemid
-> AND C.dateAdded = F.firstPost
-> ;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from getItemOwners;
+--------+--------+
| itemid | userid |
+--------+--------+
| 383 | ABC3EF |
| 163 | ABC3EF |
| 3ef | 100 |
+--------+--------+
But this is just a start.
You now have to *ALSO* find the *LAST* POST by each userid for each itemid. Because, of course, you only want to count comments that occur *AFTER* the last post by each user.
Truthfully, part of the problem is that your database organization is probably not ideal for something like this.