SELECT id, MAX(timestamp): results do not match

[nobru]

Hi,

I have the following query:

$select = "SELECT c.username, o.order_id, MAX(o.order_timestamp)";
$from = " FROM customers c, orders o";
$where = " WHERE c.username = o.username";
$group = " GROUP BY o.order_id";

$query = $select.$from.$where.$group;

The result of this query does not satisfy me, because I do not get the order_id corresponding to the max order_timestamp.
Is there a way to make them match, EXCEPT FOR using MAX(o.order_id)?

FYI, the reason why I am asking this is because as soon as I try to sort the results (ORDER BY), I cannot use "ORDER BY MAX(o.order_timestamp)" (this gives an error).

Thanks!

[ultraslacker]

I take it you are using mysql which will allow this sort of query that results in broken relations. Use the other columns in an aggregate or in GROUP BY.

You cannot use an aggregate in an ORDER BY. You can use a column alias.

[GoSharks]

i did something similar to what you are trying to do, but i did it with two seperate queries...

$query = "SELECT MAX(id) as id, screenname, 
count(timestamp) FROM log GROUP BY screenname ORDER BY `id` DESC";
$result = mysql_query($query) or die ("Error in query: $query. " . mysql_error());

while ($v = mysql_fetch_array($result))	
{
	$ida = $v[0];
	$screennamea = $v[1];
	$counta = $v[2];

$query = "SELECT DATE_FORMAT(timestamp, '%b %d, %Y %r') as timestamp FROM log WHERE id='$ida'";
$result2 = mysql_query($query) or die ("Error in query: $query. " . mysql_error());

while ($u = mysql_fetch_array($result2))	
{
	$timestampa = $u[0];
}
}

so that will grab the MAX(id), and then it will take the timestamp. something similar should work for what you are doing, if you want to use two queries