CODEWITHSUNDEEP
javajooq
Mavek
Mavek

Reputation: 63

How to orderby using alias in jooq

In the example below, how can I orderBy "amount" (which is an alias created by me using a subquery) in jooq? I cannot understand how to do it reading the documentation.

    return context
            .select(
                    NAME,
                    sum(
                            COLUMN_ONE.minus(COLUMN_TWO)
                    ).as("amount")
            )
            .from(MYTABLE)
            .groupBy(MYTABLE.NAME)
            .fetchInto(MyClass.class);

Upvotes: 1

Views: 1057

Answers (1)

Mavek
Mavek

Reputation: 63

Ok I found the solution on my own. In order to do it is necessary to create a field:

Field<BigDecimal> amount = field(
                sum(
                     COLUMN_ONE.minus(COLUMN_TWO)
                ).as("amount")
        );

And finally use it in your original query:

    return dslContext
            .select(
                    TRANSACTIONS.COIN_NAME,
                    amount
            )
            .from(MYTABLE)
            .groupBy(MYTABLE.NAME)
            .orderBy(coinAmount.desc())
            .fetchInto(MyClass.class);

Upvotes: 1

Related Questions