Forums

When a read_data Group Fields is used, it group the rows into one but show the oldest (first) record in that group.
​
Is there a way to determine which row is shown when grouped? For example, we want the latest row (highest incremental id) to be shown instead.

Is the "order by" field not showing on your install? Try reinstalling.

When we use the Group By field, it groups the records into one showing only the oldest record.
​
Changing Order By field doesn't seem to affect that, it still shows the oldest record.
​
If using SQL we could use SELECT MAX(aid) I guess, but not sure how to translate that to CC6.

By using MAX(model.aid):model.alias

It seems that even with MAX, it still proves difficult to achieve the desired results.
​
Using MAX(aid) under "Field to retrieve" with "Group fields" resulted in replacing only the aid column within the group. The other columns still show the oldest record's data.
​
Searching for solutions online, they all seem to involve subqueries (which can't seem to be translated into CC6).

What exactly is it you're trying to do? What do your columns look like?

Example database table:

Id   Name   Other_Columns
-------------------------
1    A       A_data_1
2    A       A_data_2
3    A       A_data_3
4    B       B_data_1
5    B       B_data_2
6    C       C_data_1

The desired result should be:

3    A       A_data_3
5    B       B_data_2
6    C       C_data_1

Group By selects the first row of each group only. If group by Name is used:

1    A       A_data_1
4    B       B_data_1
6    C       C_data_1

Order Fields do not affect Group By, because it seems to be applied after it. Using MAX(Id) under Field Conditions and then apply Group By doesn't work either, because it only selects the maximum Id column. We'll get this:

3    A       A_data_1
5    B       B_data_1
6    C       C_data_1

​
Example use of the above query is if you want to display each user's last message in a table logging messages. The messages will be in ascending Id chronologically (newest message have the highest Id), but Group By always show the lowest Id (oldest message).

So it doesn't work using
​

MAX(model.id):model.max_id
model.Name
model.Other_Columns

And in Group By
​

Name

?

That results in:[pre]Id Name Other_Columns max_id
---------------------------------[br]1 A A_data_1 3[br]4 B B_data_1 5[br]6 C C_data_1 6[/pre]
Because only the Id column has MAX, but the Other_Columns still retain the original row's data.[br]
Solutions we found on Stack Exchange all use either SubQuery or Inner Join.