RE: New Window Function: ROW_NUMBER_DESC() OVER() ?

Hello David, how are you?

Firstly, I apologize if I wasn't clear in what I intended to propose. I used a very specific example here, and it wasn't very clear what I really wanted to bring up for discussion.

I understand that it's possible to order the "returned dataset" using "order by ... desc." However, I would like someone to help me think about the following scenario:

Imagine I have a dataset that is returned to my front-end, and I want to reverse enumerate them (exactly the concept of Math enumerating integers). The row_number does the ascending enumeration, but I need the descending enumeration. I don't have a base column to use for "order by," and I also can't use CTID column.

Furthermore, imagine that I have a list of hashes, and I would use "order by" on this column or another column to do the reverse enumeration. This wouldn't work because I wouldn't have the correct reverse enumeration, meaning the reversal of the data would not be original.

It's not about reverse ordering; it's about reverse enumeration.

I apologize again for not being clear in the first interaction.

How can I do this without using my reversed enumeration "row_number desc" function?

Regards,
Maiquel O. Grassi.
________________________________
De: David G. Johnston <david(dot)g(dot)johnston(at)gmail(dot)com>
Enviado: terça-feira, 16 de janeiro de 2024 11:30
Para: Maiquel Grassi <grassi(at)hotmail(dot)com(dot)br>
Cc: pgsql-hackers(at)postgresql(dot)org <pgsql-hackers(at)postgresql(dot)org>
Assunto: Re: New Window Function: ROW_NUMBER_DESC() OVER() ?

On Tuesday, January 16, 2024, Maiquel Grassi <grassi(at)hotmail(dot)com(dot)br<mailto:grassi(at)hotmail(dot)com(dot)br>> wrote:
Hi developers,

I was working on loans and bank financing, specifically focusing on Amortization Systems. I had the need to reverse the counter for the total number of installments or for a specific set of installments. This "reversal" is essentially a reverse "row_number" function. I realized that it is to "hard work" to write PL/foo functions for this or even to implement it in just SQL using little code.

I think “row_number() over (order by … desc)” is a sufficient way to get this behavior and this isn’t something useful enough to warrant being the first ordering-specific function in the system.

David J.