how to write order by and limit query in jpa

Question

Possible Duplicate:
Select top 1 result using JPA

i wish to fetch top 10 results based on 'totalTradedVolume' filed of my table 'MasterScrip' when i write the following query:

Collection<MasterScrip> sm=null;
   sm=em.createQuery("select m from MasterScrip m where m.type = :type order by m.totalTradedVolume limit 2").setParameter("type", type).getResultList();

i get the following exception :

Caused by: java.lang.IllegalArgumentException: An exception occurred while creating a query in EntityManager: 
Exception Description: Syntax error parsing the query [select m from MasterScrip m where m.type = :type order by m.totalTradedVolume limit 2], line 1, column 78: unexpected token [limit].
Internal Exception: NoViableAltException(80@[])

something's wrong with my jpa query. can anyone pls correct me?

check my asnwer to a similar question http://stackoverflow.com/questions/6708085/select-top-1-result-using-jpa/6708151#6708151 — Jorge, May 23 '12 at 18:11
It is not really a duplicate. The similar question retrieves an arbitrary result, this question is about getting the result with the highest value for m.totalTradedVolume. — Jonathan Rosenne, Aug 09 '16 at 18:42
you can also check this answer https://stackoverflow.com/questions/6708085/select-top-1-result-using-jpa#answer-68849635 — Joand, Jul 20 '22 at 13:45

score 66 · Accepted Answer · edited May 04 '15 at 11:31

66

limit is not recognized in JPA. You can instead use the query.setMaxResults method:

sm = em.createQuery("select m from MasterScrip m where m.type = :type 
        order by m.totalTradedVolume")
    .setParameter("type", type)
    .setMaxResults(2).getResultList()

edited May 04 '15 at 11:31

sschrass

7,014
6
43
62

answered May 23 '12 at 18:09

Hari Menon

33,649
14
85
108

4

why does not the word "limit" work? – Koray Tugay Aug 10 '13 at 14:31
11

@KorayTugay, I know it is too late to answer your question, but to benefit others, `limit` is specific to some databasese (mysql) but `HQL` is targetted to work with all the hibernate supported database. – RP- Mar 20 '15 at 22:46

score 28 · Answer 2 · answered May 23 '12 at 18:10

28

You can work it out with Query setFirstResult and setMaxResult methods

answered May 23 '12 at 18:10

RP-

5,827
2
27
46

how to write order by and limit query in jpa

2 Answers2

Linked

Related