criteria condition in search function in model

//how to writ criteria condition for this

stationary id | remaining Stock | created |

1 100 2014-12-06 01:27:30

1 50 2014-12-07 01:27:30

1 20 2014-12-08 01:27:30

2 200 2014-12-08 01:27:30

i want to fetch the last row of each stationary_id for showing remaining stock in admin gridView.

$criteria = new CDbCriteria;

$criteria->order = "remaining Stock desc";

$criteria->group = "stationary id";

|

-------------> this Query will not give a correct output.

but this query is give a the correct output.->

SELECT stationary_id,remaining_stock ,max(created) FROM stationary_stock group by stationary_id ;

but i want to write a criteria condition in search function in model fpr admin page of GridView.

Expected output is

stationary id | remaining Stock | created

1 20 2014-12-06 01:27:30

2 100 2014-12-08 01:27:30

hi

use this code :




$criteria = new CDbCriteria;

$criteria->select = 'MAX(created) as temp'

$criteria->order = "remaining Stock desc";

$criteria->group = "stationary id";



then defined $temp as virtual attribute in your model

this Query Works For me Now :

$criteria=new CDbCriteria;

      $criteria->with = array("stationary");


      $criteria->select = 't.*';


      $criteria->join = 'LEFT JOIN stationary_stock m2 ON (t.`stationary_id` = m2.`stationary_id` AND t.id <                            m2.id)';


      $criteria->condition  = 'm2.id IS NULL';