JOOQ Window Partition ORDER DESC

user_1007927 0
Viewed 686

I try to formulate the following PostgreSQL Query into JOOQ:

SELECT * FROM
    (SELECT *, row_number() OVER (PARTITION BY propertyid ORDER BY validfrom DESC) 
        AS rownum FROM propertyvalue WHERE validfrom <= '1978-01-01T00:00:00Z') X
WHERE rownum = 1;

So I want to write a query with a nested query which uses window functions and start with the inner query first. I came that far:

ctx.select(PROPERTYALUE.fields(), DSL.rowNumber().over().
       partitionBy(PROPERTYVALUE.propertyid).orderBy(PROPERTYVALUE.validfrom).???)

After the orderBy command, I don't have a DESC option to be used anymore in JOOQ. There are just commands like rangeFollowing or rowsCurrentRow and an inherited command like desc() which returns a SortField-object on which I can't call my required subsequent commands anymore.

So is there any way in JOOQ to formulate my desired SQL query with the DESC order in a window function?

jooq postgresql java
1 Answers

Write this:

DSL.rowNumber()
   .over()
   .partitionBy(PROPERTYVALUE.propertyid)
   .orderBy(PROPERTYVALUE.validfrom.desc()))

Observe the fact that I called desc() on the column validfrom in order to pass the resulting SortField to the orderBy() clause of the window function call.

What you tried to do would have been to order by the window function expression, e.g. in an ORDER BY clause of the SELECT statement. E.g.:

SELECT *
FROM propertyvalue 
WHERE validfrom <= '1978-01-01T00:00:00Z'
ORDER BY row_number() OVER (PARTITION BY propertyid ORDER BY validfrom) DESC
user_5217990
Related