First Element Of Each Sorted Group In Group By Clause


I need some help on this:

I have a SQL table with the following structure: ID|ID_GROUP|DATA|TSTAMP

I need to find de column DATA with the highest TSTAMP of each ID_GROUP, so I need to group by ID_GROUP, sorting by TSTAM DESC and take de first element in each group. Any help on how can I do this with CDBCriteria or something similar??



I think you need explain more clear about your request. It is impossible to "group by" and "take first element of each group" within only 1 table.

Hello again.

I have a table where I have saved geographical points (lat and lng) and a timestamp for each point, I’m saving de path followed by different people. I need to get the last position (lat, lng) of each person. So in my question the data I need to select is (lat, lng), ordering by tstamp and getting the last tstamp of each person.


Are you just looking for how to write the query? Assuming your table is named ‘danih’, this query will work:

select lat, lng, tstamp

from danih, (

  select id_group, max(tstamp) as maxstamp

  from danih

  group by id_group) as d

where danih.id_group = d.id_group

and danih.tstamp = maxstamp

order by danih.id_group

The above query assumes that one person will never have 2 rows with the same tstamp. Even if they do, it would be okay as long as it’s not the max(tstamp) for that person. If you have a lot of rows in the table, you might want to benchmark the above query to make sure it performs okay.

The Yii code would look something like this:

$sql = {the above query};

$rows = Yii::app()->db->createCommand($sql)->queryAll();