We have a table in Dynamo DB, attributes to be saved are as below.
“ProcessId”(kept this as partition key, unique for each entry), “CreatedDate”(time of insertion), “Filename”, other random attributes….

We are using API gateway and integrating it with Dynamo DB directly.
We want to get the latest N records after filtering.

We thought of using Scan with limit of N, but this is not working as limit N limits the scanning before filtering and gives us inconsistent result.
We have created GSI on CreatedDate for the below post request to work.

{
  "TableName": "<TABLE NAME>",
  "Limit": $input.params('pageSize'),
  "FilterExpression": "CreatedDate > :v1",
  "ExpressionAttributeValues": {
      ":v1": {"S": "2024-05-03T06:32:22"}
  },
  "ReturnConsumedCapacity": "TOTAL",

}

We also want to sort the result set desc order, latest first.

Do you think it is possible, if not do we need to consider DB change.