help with GROUP BY syntax

[dsaba]

Hello I need some help with an GROUP BY query which is giving unexpected results

 

the query is:

$find_query = mysql_query("SELECT * FROM `text` WHERE `type`='comment' AND `link`='2' GROUP BY `by_userid` ORDER BY `post_timestamp` DESC");

 

these things are true about data in the `text` table:

1. several entries have the same `link`, for example there could be 20 entries WHERE `link`='2'

2. several entries have the same `by_userid`

 

what i'm trying to grab is only entries from unique `by_userids` and also only the unique by_userids which have the biggest `post_timestamp`

 

whats going on is that its grouping indeed `by_userid` but its taking the first entry from that by_userid instead of taking the latest entry as I tried to order it like DESC

 

then its ordering those entries which it chose, with post_timestamp DESC

 

really i'd like it to choose only the unique by_userids and the by_userids from the latest post_timestamps

 

how do I do this? i tried adding another group by like so:

GROUP BY by_userid AND post_timestamp ORDER BY post_timestamp

 

but this isn't working either, this is hard thing to wrap your brain around, I need some help with this

 

-thank you

[dsaba]

i achieved what i was aiming for with this code:

 

$find_query = mysql_query("SELECT * FROM `text` WHERE `type`='$type' AND `link`='$link' ORDER BY `post_timestamp` DESC");
$userArray[] = $submitter_userid; //now it wont update an entries for the submitter
while ($row = mysql_fetch_array($find_query)) {
$by_userid = $row['by_userid'];
	if (in_array($by_userid, $userArray) == TRUE) {
	continue;
	}
$userArray[] = $by_userid;

$id = $row['id'];
$post_timestamp = $row['post_timestamp'];
$update_string = "UPDATE `text` SET `current_timestamp`='$timestamp' WHERE `id`='$id'";
$update_query = mysql_query($update_string);
}

 

 

I still don't know how to mimic this alogorithim with pure mysql and GROUP BY, but for now i'll use this