MAX « Query « JPA Q&A





1. Using Min, Max and Count on HQL    stackoverflow.com

Does hibernate HQL queries support using select min, max, count and other sql functions? like select min(p.age) from person p Thanks

2. Select nvl(max(c.EmployeeId),0) in JPQL?    stackoverflow.com

I'm using oracle10g database and eclipselink, I need to obtain the last inserted key from the table so i've created this query

javax.persistence.Query q =   
    ...

3. Is it possible to select data with max value for a column using Criteria in Hibernate?    stackoverflow.com

Lets say I have the following mapping:

<hibernate-mapping package="mypackage">
    <class name="User" table="user">
  <id name="id" column="id">
   <generator class="native"></generator>
  </id>        ...

4. how do you get "real" sql distinct with hibernate criteria queries?    stackoverflow.com

I have a Hibernate criteria query that is incorrectly pulling out max results. In many cases, when I specify 20 max results, the query actually only returns 1 or 5 results, ...

5. jpa 2 hibernate limit (max results) to a CriteriaQuery    stackoverflow.com

maybe it's a silly question but I cannot find the answer in the docs: How can set a limit to the CriteriaQuery using JPA2? Thanks

6. HQL query for entity with max value    stackoverflow.com

I have a Hibernate entity that looks like this (accessors ommitted for brevity):

@Entity
@Table(name="FeatureList_Version")
@SecondaryTable(name="FeatureList",
    pkJoinColumns=@PrimaryKeyJoinColumn(name="FeatureList_Key"))
public class FeatureList implements Serializable {

    @Id
    @Column(name="FeatureList_Version_Key")
  ...

7. Oracle/sql/jpa - With a year-span table, how do I select rows with max data (a column) from every 7 days?    stackoverflow.com

I have a table of the following columns: Timestamp(timestamp), data(integer), id1(integer), id2(integer) My user will be able to give three inputs, start date (normalized to Sunday), end date (normalized to Saturday), and a ...

8. How do I rewrite this particular JPQL query with SELECT MAX() to CriteriaQuery?    stackoverflow.com

I am quite new to JPA and I have encountered a problem with understanding one particular query. I have rewritten in to the CriteriaQuery but the result and the query translated ...

9. hql subquery with max timestamp    stackoverflow.com

I have a HQL query

select r2.id.name, max(r2.id.vtimestamp) as vtimestamp" +
" from RRTable r2 group by r2.id.name
that works, but when I do
select rr.id.name, rr.id.vtimestamp from RRTable rr, 
(select r2.id.name, max(r2.id.vtimestamp) as vtimestamp" ...





10. translating a TypedQuery to criteriaQuery (JPA 2.0) with max, min, group by and order by    stackoverflow.com


I have some struggles in translating a well working typedQuery to a pure JPA 2.0 criteriaQuery :/ Following my working typedQuery:

String name = "Station1";

Query qry = em.createQuery("
select YEAR(s.fetchDate), MAX(s.actExport)-MIN(s.actExport), MIN(s.actExport), MAX(s.actExport) ...

11. How to select max value from n number linked tuples    stackoverflow.com

I'm trying to select the max id from any n number linked tuples in a sql server db - we are writing an upgrade script for some data sets within an ...

12. mysql query - optimizing existing MAX-MIN query for a huge table    stackoverflow.com

I have a more or less good working query (concerning to the result) but it takes about 45seconds to be processed. That's definitely too long for presenting the data in a ...

13. Max date in an nHibernate HQL query    stackoverflow.com

I am having some difficultly writing an hql query that will search a table for all the rows where certain criteria are met but I only want to return the row ...

14. MAX( ) in hibernate query    coderanch.com

15. JpA + select nvl(max(att),0)    coderanch.com

16. A simple JPA query made difficult with MAX ?    java.net

One way might be to use a seperate query to return the max values and then find the Records matching those values. This can be done in a subquery such as: "SELECT record FROM Record record WHERE Id = (SELECT max(Id) FROM Record )" Of course, this should return records with only the max ID. You could order them by versionNumber ...





17. SQL help on HQL with group by/ max function    forum.hibernate.org

Hi, Can someone help me on this problem i have... I'm using hibernate...and have a table named case_score_t with these columns: id, case_id, user_id, score, created_date I am interested in picking the rows with the greatest created_date, for a certain user_id. I have this SQL currently "SELECT cs FROM CaseScore cs WHERE cs.user.id = ? GROUP BY cs.c.id ORDER BY cs.created ...

18. how to group by max(date)?    forum.hibernate.org

I'm struggling to build a HQL query against a customer invoice table where I want to return a list of the most recent invoices for each customer but across all customers, like... Here's a sample full table... Code: [customer id] [invoice date] [invoice number] [invoice value] 1 ...

19. Using MAX in the subquery of a LEFT JOIN    forum.hibernate.org

I have a one to many relation between table A and table B, and I need to get the row with latest date <= now() from table B from within a LEFT JOIN's subquery. The hql query that I am using is returning all the dates and ignore my subquery in the WHERE clause, but when i translate the same query ...

20. scalar query using max() not working    forum.hibernate.org

Hi, I want to retrieve the max value for a column and I followed the example code for scalar queries except for the (Object []) cast, I got a classCast exception without it. Now I am getting a null pointer exception. The message "Wierd. row is null" gets printed. I tried to run it with and without valid data in the ...

21. Using aggregate function max(), min()..    forum.hibernate.org

Iterator results = sess.iterate( "select cat.color, min(cat.birthdate), count(cat) from Cat cat " + "group by cat.color" ); while ( results.hasNext() ) { Object[] row = results.next(); Color type = (Color) row[0]; Date oldest ...

22. Problem with select max()    forum.hibernate.org

I have the below HQL Query (with postgres) select up from UserPromotion up where up.user = :user and up.createTimestamp = (select max(u.createTimeStamp) from UserPromotion u where u.user = :user); Unfortunately, when translated, the SQL query becomes, select userpromot0_.id as id, userpromot0_.version as version, userpromot0_.credit as credit, userpromot0_.Promotion as Promotion, userpromot0_.user_info as user_info, userpromot0_.last_mod_by as last_mod6_, userpromot0_.last_mod_time as last_mod7_, userpromot0_.created_by as created_by, ...

23. Criteria, Pagination and Number of Rows Available (Max Pgs)    forum.hibernate.org

Since this isn't supported by default, I started doing some testing, and my suggestion isn't acceptable. Code: Session session = SpringSupport.getSession() ; long startTime = System.currentTimeMillis() ; mLogger.warn(Float.toString(this.secondsLater(startTime))) ; Criteria criteria = session.createCriteria(MyCustomObject.class) ; ...

24. Finding max in HQL    forum.hibernate.org

I am developing an application with Hibernate2.1.4 and JBoss-3.2.7. I want to write a query for finding a max value of a particular column in a table. How can I give this? Can anyone give me an example. I tried giving like the below: "select max(Settlement.SettlementPK.SettlementId) from Settlement" but it returns the all the data. I want only one row of ...

25. Using Max whith CreateSQLQuery    forum.hibernate.org

26. NOOB: limiting the max results returned by a query?    forum.hibernate.org

Hi all, I'm just getting started with hibernate, and I am no DB expert... Anyways the basic question is: Given A Query may return say 1 million rows, retrieving all 1 million records into a list..is obviously going to be memory issue. so..should the query result in in that many matching records, how can I specify a max return size?, and ...

27. select max not working with createSQLQuery    forum.hibernate.org

StringBuffer sb = new StringBuffer(20); sb.append("select MAX(IFNULL("); sb.append(primaryKeyColumn.getName()); sb.append(", 0)) as maxPrimayKey from "); sb.append(tableName); Connection connection = session.connection(); PreparedStatement statement = null; ResultSet rs = null; int maxPrimaryKeyValue = -1; ...

28. HQL Query on collection size & elements min-max values    forum.hibernate.org

Need help with Hibernate? Read this first: http://www.hibernate.org/ForumMailingli ... AskForHelp Hibernate version: 3 Name and version of the database you are using: 10g I am having problem to apply these 2 criterias (Collection's size & Collection elements' min-max) together. I can apply Collection-Size in HQL-Query, but unable to get it to work in Criteria. Also, applying the min-max on Collecion elements, ...

29. Help: Projections.max    forum.hibernate.org

30. using the max(...) function with the Ast Query parser    forum.hibernate.org

Hibernate version:3.1 I am in the midst of migrating our application from the classic query parser to the ast query parser. One of the issues I am trying to solve is in regards to the max() function. In the classic query parser I could run the query: "select max(login) from UserLogin login" and I would recieve a UserLogin object back from ...

32. Max of row count    forum.hibernate.org

List l = q.getResultList(); em.getTransaction().commit(); Object[] os; Object o; try { os = (Object[])l.get(0); o = os[0]; logger.debug("The SELECT clause for this query had more than one element."); } catch(ClassCastException cce) { logger.debug("The SELECT clause for this query only had one element."); ...

33. Max results in where clause for Query By Criteria in Java    forum.hibernate.org

Hello, I've been working with hibernate for a bit and was wondering how to transform the following query to Hibernate Query By Criteria in Java? Code: SELECT table1.* FROM table1 INNER JOIN table2 ON table1.field1 = table2.tbl1Field1 GROUP BY table1.field1 ORDER BY MAX(table2.numField); I've tried this: Code: Criteria myCrit = session.createCriteria(Table1.class); ...

34. help : select max(userID)    forum.hibernate.org

Hi, I am new to hibernate. Could somebody please help me? Thank you very much. String hql= "select max(userID) from User"; List list = session.createQuery(hql).list(); int maxID = ( (Integer)list.get(0) ).intValue(); Error message: Exception in thread "main" java.lang.IllegalStateException: No data type for node: org.hibernate.hql.ast.tree.IdentNode +-[IDENT] IdentNode: 'userID' {originalText=userID} at org.hibernate.hql.ast.tree.SelectClause.initializeExplicitSelectClause(SelectClause.java:140) at org.hibernate.hql.ast.HqlSqlWalker.useSelectClause(HqlSqlWalker.java:702) at org.hibernate.hql.ast.HqlSqlWalker.processQuery(HqlSqlWalker.java:531) at org.hibernate.hql.antlr.HqlSqlBaseWalker.query(HqlSqlBaseWalker.java:645) at org.hibernate.hql.antlr.HqlSqlBaseWalker.selectStatement(HqlSqlBaseWalker.java:281) at org.hibernate.hql.antlr.HqlSqlBaseWalker.statement(HqlSqlBaseWalker.java:229) at ...

35. Pagination Large Result Set setFirst/Max vs. Scrollable    forum.hibernate.org

Hi All, I'm working on pagination with some large results sets (1M+) in DB2. My initial thought was that a scrollable result set would perform better than setting the first and max row. My thinking was that towards the end of the result set (first row = 100K max row = 50) DB2 would be sending me the first 100K rows ...

36. Need help with HQL to find max collection size    forum.hibernate.org

public class Physician extends IdEntity { private List siteList; @ManyToMany(mappedBy="physicianList") public List getSiteList() { return siteList; } } public class Site extends IdEntity { private List physicianList; ...

37. Max aggregate function not returning same type of argument    forum.hibernate.org

import java.sql.Timestamp; public class EventEntry { private String sender; private Timestamp lastEventTime; public EventEntry(String sender, Timestamp lastEventTime) { this.sender= sender; this.lastEventTime = lastEventTime; } ... ...

38. Aggregate functions (max)    forum.hibernate.org

...

39. hql max of count    forum.hibernate.org

40. do select(*) with max number of results w/ Criteria api    forum.hibernate.org

Hi, For performance reasons, I want to limit the number of counted rows in a count(*) query. I can do this in sql as follows: select count(*) from (select id from USER u where ... limit 10000 ) as tmpResult I've been trying to do this using the Hibernate Criteria API but unfortunately I'm getting nowhere. Is such a simple thing ...

41. HQL for retrieving an object with a max clause    forum.hibernate.org

I have a table full of addresses. In this table, multiple copies of the same address or stored, but they become deprecated or considered old addresses. So what I need is the current address, and the current address is the one that has the highest ID [max(id)]. I'm trying to retrieve the object but I can't get the clause correct; something ...

42. Hibernate Query, max results, etc    forum.hibernate.org

Hi. I am looking at using a hibernate query object for retrieving data from the database for use by a user interface. As such, I need the data paginated. The methods uery.setMaxResults and Query.setFirstResult look useful for this, but I do have a question: When Hibernate returns the specified result range, does it actually generate the native SQL that specifies the ...