First Element Of Each Sorted Group In Group By Clause

Yii 1.1 General Discussion
1

Hi!

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??

Greats!,

Daniel

2

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.

3

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.

Thanks.

4

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();