get me the latest Change from Select Query in below given condition

Question

I have a Table structure as

id, trackid, table_name, operation, oldvalue, newvalue, field, changedonetime

Now if I have 3 rows for the same "trackid" same "field", then how can i select the latest out of the three?

i.e. for e.g.:

id = 100 trackid = 152 table_name = jos_menu operation= UPDATE oldvalue = IPL newvalue = IPLcccc field = name live = 0 changedonetime = 2010-04-30 17:54:39

and

id = 101 trackid = 152 table_name = jos_menu operation= UPDATE oldvalue = IPLcccc newvalue = IPL2222 field = name live = 0 changedonetime = 2010-04-30 18:54:39

As u can see above the secind entry is the latest change, Now what query I should use to get the only one and Latest row out of many such rows...

$distupdqry = "select DISTINCT trackid,table_name from jos_audittrail where live = 0 AND operation = 'UPDATE'";
$disupdsel = mysql_query($distupdqry);
$t_ids = array();
$t_table = array();
while($row3 = mysql_fetch_array($disupdsel))
{ 
 $t_ids[] = $row3['trackid'];
 $t_table[] = $row3['table_name'];
 //$t_table[] = $row3['table_name'];
}

//echo "<pre>";print_r($t_table);echo "<pre>";

//exit;
for($n=0;$n<count($t_ids);$n++)
{

   $qupd = "SELECT * FROM jos_audittrail WHERE operation = 'UPDATE'  AND trackid=$t_ids[$n] order by changedone DESC ";
 $seletupdaudit = mysql_query($qupd);
 $row4 = array();
 $audit3 = array();
 while($row4 = mysql_fetch_array($seletupdaudit))
 { 
  $audit3[] = $row4; 
 }   


  $updatefield = '';
  for($j=0;$j<count($audit3);$j++)
  {
   if($j == 0)
   {

    if($audit3[$j]['operation'] == "UPDATE")
    {
       //$insqry .= $audit2[$i]['operation']." ";
       //echo "<br>";
     $updatefield .= "UPDATE `".$audit3[$j]['table_name']."` SET ";

    }
   }

   if($audit3[$j]['operation'] == "UPDATE")
   {

    $updatefield .= $audit3[$j]['field']." = '".$audit3[$j]['newvalue']."', ";

   }

  }
  /*echo "<pre>";
 print_r($audit3);
 exit;*/
  $primarykey = "SHOW INDEXES FROM `".$t_table[$n]."` WHERE Key_name = 'PRIMARY'";
  $prime = mysql_query($primarykey);
  $pkey = mysql_fetch_array($prime);
  $updatefield .= "]";
     echo $updatefield = str_replace(", ]"," WHERE ".$pkey['Column_name']." = '".$t_ids[$n]."'",$updatefield); 
}

In the above code I am fetching ou the distinct IDs in which update operation has been done, and then accordingly query is fired to get all the changes done on different fields of the selected distinct ids... Here I am creating the Update query by fetching the records from the initially described table which is here mentioned as audittrail table... Therefore I need the last made change in the field so that only latest change can be selected in the select queries i have used...

please go through the code.. and see how can i make the required change i need finally..

Answer 1

This is another question of the greatest-n-per-group category, which comes up several times per week on Stack Overflow.

Here's how I'd solve it in your case:

SELECT j1.*
FROM jos_audittrail j1 LEFT OUTER JOIN jos_audittrail j2
  ON (j1.trackid = j2.trackid AND j1.field = j2.field 
  AND j1.changedonetime < j2.changedonetime)
WHERE j2.id IS NULL;