0

I'm trying to execute the following SQL query writing native query in JPA:

            SELECT id FROM (
                SELECT DISTINCT id, DENSE_RANK() OVER (ORDER BY id) AS row FROM table
            ) WHERE row BETWEEN 1 AND 10;

In the Query annotation I wrote:

@Query(value = "SELECT t.id FROM (SELECT DISTINCT(e.id), DENSE_RANK() OVER (ORDER BY e.id) AS row FROM Table e) t WHERE row BETWEEN ? AND ?")

but I have syntax errors here FROM (SELECT. Please, can you help me?

M.Love
  • 23
  • 5
  • Does this help? https://stackoverflow.com/questions/12943574/spring-data-jpa-select-distinct – pringi Feb 10 '22 at 11:42
  • Or maybe you need to add an alias: SELECT id FROM ( SELECT DISTINCT id, DENSE_RANK() OVER (ORDER BY id) AS row FROM table ) t WHERE row BETWEEN 1 AND 10; – pringi Feb 10 '22 at 11:49
  • you have missed to add `nativeQuery=true`. please add it your end of query. – Geeth Feb 13 '22 at 05:20

1 Answers1

0

You have probably forgot nativeQuery=true Try this:

@Query(value = "SELECT t.id FROM (SELECT DISTINCT(e.id), DENSE_RANK() OVER (ORDER BY e.id) AS row FROM Table e) t WHERE row BETWEEN ? AND ?", nativeQuery = true)
José Mateus Barros Taveira
  • 104
  • 2