Read Data "Group Fields" to show newest row

webbusteruk 03 Jan, 2019
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.
healyhatman 03 Jan, 2019
Is the "order by" field not showing on your install? Try reinstalling.
webbusteruk 04 Jan, 2019
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.
healyhatman 04 Jan, 2019
By using MAX(model.aid):model.alias
webbusteruk 05 Jan, 2019
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).
healyhatman 05 Jan, 2019
What exactly is it you're trying to do? What do your columns look like?
webbusteruk 06 Jan, 2019
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).
healyhatman 06 Jan, 2019
So it doesn't work using
And in Group By
webbusteruk 06 Jan, 2019
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.
This topic is locked and no more replies can be posted.