AR compare similar records and return most recent

Hi folks,

I’d like know if there are a way (using AR criteria or scopes) to get records on a table and return the most recent?

I’m tryin to do it using a timestamp field and and two records that have the same values.




+-------------+---------+---------+---------+---------------------+

|          id | colour1 | colour2 | colour3 | timestamp           |

+-------------+---------+---------+---------+---------------------+

|      313025 | Red     | Yellow  | NULL    | 2008-11-12 15:20:11 |

|      313025 | Green   | Green   | Green   | 2008-11-12 15:21:28 |

|      313025 | Green   | Green   | Green   | 2008-11-12 15:22:27 |

+-------------+---------+---------+---------+---------------------+



The idea is, when I find the records on the table above I’d like receive some thing like:





+-------------+---------+---------+---------+---------------------+

|          id | colour1 | colour2 | colour3 | timestamp           |

+-------------+---------+---------+---------+---------------------+

|      313025 | Green   | Green   | Green   | 2008-11-12 15:22:27 |

|      313025 | Red     | Yellow  | NULL    | 2008-11-12 15:20:11 |

+-------------+---------+---------+---------+---------------------+




Where the most recent record where the colour 1 show up, and the table order is based on timestamp desc.

Somebody know how can I do it?

Thanks.

Hi,

Try with using below criteria

$criteria=new CDbCriteria;

$criteria->select=‘id,DISTINCT(colour1),colour2,colour3,timestamp’;

$criteria->order=‘timestamp DESC’;

Aruna

Hi Aruna Attanayake,

there are anyway to do it without select individual columns?

Thanks.