# How to get the most recent date as a sub-query **URL:** \ **Category:** API & Programming\ **Created:** [August 13, 2021, 1:00am UTC](https://community.fibery.io/t/how-to-get-the-most-recent-date-as-a-sub-query/1916 "2021-08-13T01:00:58Z")\ **Posts on this page:** 7\ **Page:** 1
**Author:** ![Dimitri\_S](https://sea2.discourse-cdn.com/flex020/user_avatar/community.fibery.io/dimitri_s/32/1683_2.png) [@Dimitri\_S](https://community.fibery.io/u/Dimitri_S)\ **Post date:** [August 13, 2021, 1:00am UTC](https://community.fibery.io/t/how-to-get-the-most-recent-date-as-a-sub-query/1916/1 "2021-08-13T01:00:59Z")
I have an “Accounting” app that has two types: **Client** and **Invoice**. There is a relation where a client can have many invoices. Lets pretend the Client type only has one field: Name The Invoice type has two important fields: Invoice Period (a date field that includes start & end dates) and Date Invoiced (a normal date field). What I want to do is an API query that returns the Name of each client and their most recent Date Invoiced. **EDIT:** This is what I have so far but I think that by using `"q/limit": 1` I am only getting a single Date Invoiced value, where as I expected it to work more like a sub-query. So that’s actually not going to work. ```auto "query": { "q/from": "Accounting/Client", "q/select": [ "Accounting/Name", { "Accounting/Invoices": { "q/select": ["Accounting/Date Invoiced"], "q/order-by": [ [["Accounting/Date Invoiced"], "q/desc"] ], "q/limit": 1 } } ], "q/limit": "q/no-limit" } ``` ---
**Author:** ![Chr1sG](https://sea2.discourse-cdn.com/flex020/user_avatar/community.fibery.io/chr1sg/32/3941_2.png) [@Chr1sG](https://community.fibery.io/u/Chr1sG)\ **Post date:** [August 13, 2021, 5:48am UTC](https://community.fibery.io/t/how-to-get-the-most-recent-date-as-a-sub-query/1916/2 "2021-08-13T05:48:38Z")
Have you considered adding a formula field in Fibery to calculate the date of the most recent invoice, and then just using the API to retrieve that field? ---
**Author:** ![Sergey\_Truhtanov](https://sea2.discourse-cdn.com/flex020/user_avatar/community.fibery.io/sergey_truhtanov/32/13986_2.png) [@Sergey\_Truhtanov](https://community.fibery.io/u/Sergey_Truhtanov)\ **Post date:** [August 13, 2021, 9:35am UTC](https://community.fibery.io/t/how-to-get-the-most-recent-date-as-a-sub-query/1916/3 "2021-08-13T09:35:54Z")
Hello. Unfortunately api does not support other q/limit, than “q/no-limit” for subqueries at the moment. There is a bug on our side, that we does not throw meaningful exception in your case. Unfortunately even @Chr1sG workaround is not possible for now, as you cannot create such formula (we are implementing such capabilities right now, so it will be released more or less soon). Can only suggest to stick with “q/no-limit” for now and take first item on your client side, sorry. ---
**Author:** ![Chr1sG](https://sea2.discourse-cdn.com/flex020/user_avatar/community.fibery.io/chr1sg/32/3941_2.png) [@Chr1sG](https://community.fibery.io/u/Chr1sG)\ **Post date:** [August 13, 2021, 10:22am UTC](https://community.fibery.io/t/how-to-get-the-most-recent-date-as-a-sub-query/1916/4 "2021-08-13T10:22:05Z")
> [@Sergey\_Truhtanov](#): > > as you cannot create such formula Isn’t a formula field `Invoices.max([Date invoiced])` for the `Client` type all that Dimitri needs? And then use the API query to get the value of this field for each Client? Or did I misunderstand the problem? ---
**Author:** ![Viktar\_Zhuk](https://sea2.discourse-cdn.com/flex020/user_avatar/community.fibery.io/viktar_zhuk/32/1934_2.png) [@Viktar\_Zhuk](https://community.fibery.io/u/Viktar_Zhuk)\ **Post date:** [August 13, 2021, 11:41am UTC](https://community.fibery.io/t/how-to-get-the-most-recent-date-as-a-sub-query/1916/5 "2021-08-13T11:41:23Z")
Actually if you only need Date w/o any other fields you may try aggregate function q/max. See example bellow ```auto "query": { "q/from": "Accounting/Client", "q/select": { "Accounting/Name": "Accounting/Name", "most-recent-invoice-date": ["q/max", ["Accounting/Invoices", "Accounting/Date Invoiced"]] }, "q/limit": "q/no-limit" } ``` ---
**Author:** ![Dimitri\_S](https://sea2.discourse-cdn.com/flex020/user_avatar/community.fibery.io/dimitri_s/32/1683_2.png) [@Dimitri\_S](https://community.fibery.io/u/Dimitri_S)\ **Post date:** [August 13, 2021, 1:39pm UTC](https://community.fibery.io/t/how-to-get-the-most-recent-date-as-a-sub-query/1916/6 "2021-08-13T13:39:00Z")
@Chr1sG No you are correct. I basically ended up creating helper fields with `Invoices.Max([Date Invoiced])` and `Invoices.Max([Invoice Period].End)`. I guess the point of this post was to see if it could be done purely in the API. @Viktar_Zhuk Nice, that works pretty well for simple dates. However it looks like this approach doesn’t work with date range type dates as I get an error: `ERROR: function max(daterange) does not exist` when trying to do `"q/max", ["Accounting/Invoices", "Accounting/Invoice Period"]` @Sergey_Truhtanov Any ongoing plans to add `max()`/`min()` support for date ranges? ---
**Author:** ![Sergey\_Truhtanov](https://sea2.discourse-cdn.com/flex020/user_avatar/community.fibery.io/sergey_truhtanov/32/13986_2.png) [@Sergey\_Truhtanov](https://community.fibery.io/u/Sergey_Truhtanov)\ **Post date:** [August 19, 2021, 10:41am UTC](https://community.fibery.io/t/how-to-get-the-most-recent-date-as-a-sub-query/1916/7 "2021-08-19T10:41:43Z")
Sorry for late response. Unfortunately it is not possible via api at the moment. There are “q/end”, “q/start” functions in api that you can use to extract parts from date-range, but you cannot use them with `max` for nested collections. Cannot promise any eta right now.