Hibernate: how to select last N rows in ascending

Question

Hibernate version: 5.2

I am trying to use subqueries to do, and use setMaxResults(int).

session.createQuery(
"FROM ( SELECT * FROM tickets ORDER BY id DESC limit 3) sub ORDER BY id ASC"
);

However, HQL subqueries can occur only in the select or where clauses, and limit can't be used in hibernate.

How can I do it in hibernate?

---

Update - To make it clear

For eg, there are 10 data entries from id=1 to id=10.

I want to select last 3 data in ascending order of id by only one query + without further data processing.

The result from db would be id=8 to id=10

Thank You.

Answer 1

Assuming you have the ids 1 to 10 and you want the last N=3.

Taken your approach

SELECT * FROM tickets ORDER BY id DESC

would return the ids in order from 10 to 1. You then want to get the last N=3 in ascending order. Which means you want the ids 3 to 1 in ascending order.

What would be wrong with selecting the first N ids in ascending order?

session.createQuery(
    "FROM tickets ORDER BY id ASC"
).setMaxResults(n);

Answer 2

You can use Query setFirstResult(int startPosition), Query setMaxResults(int maxResult) to implement it. Typically used in the pagination.

Opportunistic, you can get the first 3 record descendingly here, so you can only use the Query setMaxResults(int maxResult).