Criteria Query and Pagination in Spring Data JPA

Question

How to write below query using Criteria in spring data jpa with Pagination. Below query is responsible to group by specific column and to take the minimum ID from the result. I am expecting output in the format of List.

WITH YourSample (ID, VERSION_ID) AS 
(
  SELECT 111, 'ABC1234.1'   FROM dual UNION ALL
  SELECT 222, 'ABC1234.2'   FROM dual UNION ALL
  SELECT 333, 'ABC12345.1'  FROM dual UNION ALL
  SELECT 444, 'ABC12345.2'  FROM dual UNION ALL
  SELECT 555, 'ABC123456.1' FROM dual UNION ALL
  SELECT 666, 'ABC123457.1' FROM dual
)
SELECT 
    MIN(ID)KEEP(dense_rank FIRST ORDER BY ID) ID
FROM YourSample t
GROUP BY SUBSTR( VERSION_ID, 1, INSTR(VERSION_ID, '.')-1 )
ORDER BY ID;

Table Link: https://dbfiddle.uk/ciaZOdm0

Criteria Query and Pagination in Spring Data JPA

0 Answers