Hi Nicolás,

1. Create an input parameter YearFilter of the same data type as the YEAR column
2. Set in the aggregate the filter: YEAR <= YearFilter
3. Set the Sort By in the aggrate on YEAR DESC
4. Set the Max Records on the aggregate to 2.

The result set wil have 0, 1 or 2 records based on your year filter value.

Regards,

Daniel

Hi Nicolas,

I'm not sure I understand why you would want that, can you explain more ?  For what reason do you already want to retrieve the next one ?  

What do you mean by 'I put a number' ?  Do you just guess a year that might be in the database and enter it somewhere ?

What is the meaning of the 'next' one.  Do you have some sort of ordering attribute that is not shown in your example data, or is it maybe on year descending ??

Let's say it's on descending year, and you somehow know what the 'current' is, let's say you somehow have 1920 to start with.  

Then you can just use an aggregate getting all records sorted on descending year, whit as filter year <= current year

the 'current' will be Aggregatename.list[0] and the 'next' will be Aggregatename.list[1]

But again, this seems a bit of an odd requirement, maybe you can tell more about what you would like to accomplish ?

Dorine

Hi Nicolas,

You can try this type of SQL, I did it with User Entity.

Result is coming like ->

@Sourav Pasari,

Your solution does not answer the question in best manner.

For the given problem no advanced query is necessary.

Regards,

Daniel