Skip to main content
G
gauravp0003
March 4, 2021
Question

extract highest row from array

  • March 4, 2021
  • 6 replies
  • 0 views

Team,

I have an array of results from query and want to use the rows which have latest timestamp.

So array have data like (username :abc,id,1,timestamp1) , same user have another entry but with different timestamp. Max is not doing the trick , any idea ?

    6 replies

    manuelh0002
    manuelh0002
    March 4, 2021

    Does the Dataset comes from the DB?

    If yes, you can use a!paginginfo and with the a!sort get the data in chronological order.

    G
    gauravp0003Author
    March 4, 2021

    Hello Manuel,

    Thank you . Yes , using sort info i am able to get the top rows sorted in order but as said there are many rows in returned dataset and i want the latest top rows out to show in grid as unique data else multiple users are appearing in grid , i want unique usernames with latest timestamp.

    peter.lewis
    peter.lewis
    Employee
    March 4, 2021

    You can do this using an aggregation. If you group by the username and return the MAX of the date, then it should return the most recent item. Here's an example:

    a!queryEntity(
  entity: cons!YOUR_DSE,
  query: a!query(
    aggregation: a!queryAggregation(
      aggregationColumns: {
        a!queryAggregationColumn(
          field: "username",
          isGrouping: true
        ),
        a!queryAggregationColumn(
          field: "dateUpdated",
          alias: "dateUpdated_max",
          aggregationfunction: "MAX",
        )
      }
    ),
    pagingInfo: a!pagingInfo(
      startIndex: 1,
      batchSize: 50,
      sort: a!sortInfo(
        field: "dateUpdated_max",
        ascending: false
      )
    )
  ),
  fetchTotalCount: false
)

    Terms & ConditionsAccessibility statement