View Single Post
Old 12-08-2012, 09:23 PM   PM User | #4
Old Pedant
Supreme Master coder!

 
Old Pedant's Avatar
 
Join Date: Feb 2009
Posts: 23,200
Thanks: 59
Thanked 3,996 Times in 3,965 Posts
Old Pedant is a name known to allOld Pedant is a name known to allOld Pedant is a name known to allOld Pedant is a name known to allOld Pedant is a name known to allOld Pedant is a name known to all
I think I have your answer. You'll need to try it with some real data. After you fix your table design, one hopes.

I created a simplified version of your table with only the relevant fields.
Code:
mysql> select itemid, userid, dateAdded from itemComments order by itemid, dateAdded;
+--------+--------+---------------------+
| itemid | userid | dateAdded           |
+--------+--------+---------------------+
| 383    | ABC4EF | 2012-11-16 23:39:22 |
| 383    | 1      | 2012-12-08 09:46:41 |
| 383    | 100    | 2012-12-08 09:47:48 |
| 777    | 1      | 2011-01-01 00:00:00 |
| 777    | ABC4EF | 2012-01-01 00:00:00 |
| 777    | 2      | 2013-01-01 00:00:00 |
+--------+--------+---------------------+
And then here is the the query I came up with and its results:
Code:
mysql> SELECT C.itemid, C.userid, COUNT(*)
    -> FROM itemComments AS C,  (
    ->      SELECT itemid, userid, MAX(dateadded) AS lastComment
    ->      FROM itemComments
    ->      GROUP BY itemid, userid ) AS M
    -> WHERE C.itemid = M.itemid
    -> AND C.dateadded < M.lastcomment
    -> GROUP BY C.itemid, C.userid
    -> ORDER BY C.itemid, C.userid
    -> ;
+--------+--------+----------+
| itemid | userid | COUNT(*) |
+--------+--------+----------+
| 383    | 1      |        1 |
| 383    | ABC4EF |        2 |
| 777    | 1      |        2 |
| 777    | ABC4EF |        1 |
+--------+--------+----------+
As you can see, that is saying that (to pick one example);:
Quote:
For itemid 777, there are two comments made by others after userid 1's last comment.
No?
__________________
An optimist sees the glass as half full.
A pessimist sees the glass as half empty.
A realist drinks it no matter how much there is.
Old Pedant is offline   Reply With Quote