Get records with the most recent date for each group

Jacki Soikis

Hello everyone,
I want to know how to get the records with the most recent date for each group. Similar to this example:
a | 3/20/2022 |76
a | 2/22/2022 |80
a | 2/12/2022 |50
a | 3/16/2022 |40
b | 3/17/2022 |44
b | 3/18/2022 |44
c | 2/12/2022 |50

Desired result:

a | 3/20/2022 |76
b | 3/18/2022 |44
c | 2/12/2022 |50

Thanks

Ray Deck

Are you looping through records in your function stack? That should be a straightforward single loop through the record set to come out with the filtered list. You’ll need to use for each and have a json object most likely to capture the maximum values, then fetch the values from that object into an array at the end. All things xano can do and would take about 30m I imagine. Let me know if you’d like to look at this together!

Jacki Soikis

 Hi,
thank you for the answer. I thought that there is a solution by using a query and that will improve the response time.

Ray Deck

 You can do this with  sequential queries if these are database tables - use one to get the max date by group, and then loop over the groups to get the first record that matches both the group and the date.  

While this is available to you, I'm not sure that's going to have a big performance boost in the near term- the nature of the question you are asking is not the kind of logic Postgresql will do very efficiently. The main benefit will be not transferring so much data from postgresql into memory that your function stack handles. You might just trying an approach and clocking it. If it is fast enough, put it down and move on to the next task. If not, try something different! (And caching approaches can make you insensitive to performance depending on the situation)

At scale there are always more tricks one can play, but scale happens later, and there may be more features available to you then too as a result of develompent by the Xano team or your own upgraded level of account to handle your growing business!