ABAP CDS Read most updated single record checking a field

[JaimeRodriguez]

Hi everyone,

I have the next table

| KEY | FIELD1 | FIELD2 | CHANGED |

| 1 | 001 | TEXT1 | 01/02/2023 |

| 2 | 001 | TEXT2 | 02/02/2023 |

| 3 | 002 | TEXT3 | 03/02/2023 |

I need to build a CDS that returns me the last changed record comparing FIELD2, so the result should be

| KEY | FIELD1 | FIELD2 | CHANGED |

| 2 | 001 | TEXT2 | 02/02/2023 |

| 3 | 002 | TEXT3 | 03/02/2023 |

Is it possible?

Thanks in advance!

[keremkoseoglu]

The sensible way of doing this is to write a CDS table function, and do the heavy lifting in AMDP / SqlScript.

To make this with pure CDS, you can split the query into multiple CDS views; which (in my opinion) won't make sense in terms of simplicity and performance. CDS1 would return the MAX( CHANGED ) (grouped by FIELD2). CDS2 would join CDS1 with the table, matching FIELD2 and MAX( CHANGED ). But, you may get multiple results for each FIELD2.

[JaimeRodriguez]

Hi Kerum,

I can't do this way, because the records were initially loaded from a file, so some of them have the same changed date.

I build a table function, but it takes 3.5 seconds for 3200 entries.

[Marian_Zeis]

Depending on your table you could to a group by with MAX( CHANGED ). Something like:

Select KEY, FIELD1, FIELD2, MAX(CHANGED) as lastChangedAt from table

As alternative you could do the grouping in a seperate CDS View and Join the grouped table on KEY, FIELD1 and FIELD2. Like

define root view entity ZI_MR_SONABL_BP
  as select distinct from table
join table_grouped on table.key = table_grouped.key and table.field1= table_grouped.field1 and table.changed = table_grouped.changed