[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 6910
  • Last Modified:

How to sort a HQL query by a colum and just get the top record?

Hi, I have a method that executes a HQL and returns me a "List" object. See the code below. Executing it right now would give me 3 records. See operatorTable.jpg. Take note that the "ID" column is a String. I would like to create a similar method that would return me the largest operator id, which in this case, is "00003". See "findMaxOperatorID()" in my code below.

May I know what's wrong with my query? Thanks.
public List findAll() {
        try {
           String queryString = "from Operator";
           Query queryObject = getSession().createQuery(queryString);
  		 return queryObject.list();
        } catch (RuntimeException re) {
           throw re;
        }
  	}
 
-----
 
    public String findMaxOperatorID() {
        try {
           String queryString = "select top 1 operator.operatorId from Operator as operator order by operator.operatorId desc";
           Query queryObject = getSession().createQuery(queryString);
  		 return queryObject.list().get(0).toString();
        } catch (RuntimeException re) {
           throw re;
        }
  	}

Open in new window

operatorTable.jpg
0
killdurst
Asked:
killdurst
2 Solutions
 
chaitu chaituCommented:
select top 1 operator.operatorId from Operator as operator order by operator.operatorId desc

may be above query will nto work in HQL .instead of this,you can write like this..

select max(operator.operatorId) from Operator as operator order by operator.operatorId desc


0
 
objectsCommented:
remove the top from query and use:

                 return queryObject.setMaxResults(0).list().get(0).toString();

0
 
killdurstAuthor Commented:
Great! Both of those work. But objects, I had to change the setMaxResults(0) to setMaxResults(1) to get it to work. Thanks guys.
0
 
mahomeCommented:
In the max-Solution "order by" is not necessary

Just a typo in setMaxResults: should be setMaxResults(1) instead of setMaxResults(0)
0
 
objectsCommented:
sorry for the typo :)

0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now