1. How do you create a Distinct query in HQL stackoverflow.comIs there a way to create a Distinct query in HQL. Either by using the "distinct" keyword or some other method. I am not sure if distinct is a ... |
2. Using DISTINCT in JPA stackoverflow.comWhat column does DISTINCT work with in JPA and is it possible to change it? Here's an example JPA query using DISTINCT: |
3. NHibernate How to Select distinct objects based on specific property using HQL? stackoverflow.comHow can HQL be used to select specific objects that meet a certain criteria? We've tried the following to generate a list of top ten subscribed RSS feeds (where SubscriptionCount is a ... |
4. Nhibernate: distinct results in second level Collection stackoverflow.comI have an object model like this: |
5. Get distinct months from database using HQL stackoverflow.comI have a |
6. Is it possible to use COUNT with a DISTINCT JPA projection? stackoverflow.comI am using a JPA distinct projection to get some data: |
7. how to create a hibernate distinct query stackoverflow.comI have taken a look around the web and can not really find the clear answer on this. I have two tables A and B. B is a child of A. ... |
8. Using Hibernate's Criteria and Projections to Select Multiple Distinct Columns stackoverflow.comUsing Hibernate's Criteria, I wan't to execute the equivalent of: |
9. Distinct + Order By in Hibernate stackoverflow.comRelated to my last question
I have worked with that answer, but now I require to do with a bit modification using hibernate.
I need to now add a string to |
10. hibernate Join with criteria with distinct won't work stackoverflow.comwhen i run i got resulted query where i expect Actual Query Actual Query(to be Produce, Desire Query) distinct [ID] |
11. Hibernate distinct clause problem stackoverflow.comI searched a lot around but cannot find the answer: I have the following SQL query: |
12. NHibernate HQL distinct and order by problem stackoverflow.comI've recently upgraded from NHibernate 1.2 to 3.1 in an old code base. I've fixed most problems but I'm stuck on this one. (I can't change from HQL to another access ... |
13. How to do a distinct count in JPA critera API? stackoverflow.comI would like to do this but with the criteria API instead: |
14. HQL distinct set stackoverflow.comI have an entity that contains a set of other entities. |
15. Hibernate criteria with Distinct and order by stackoverflow.comcan anyone tell me how to use hibernate criteria with Distinct and order by. i've been looking for a solution but can't seem to find one thanks. |
16. Hibernate new keyword with distinct stackoverflow.comI need to take hql that is currently : |
17. error in getting result when fire distinct query in JPA coderanch.com |
18. hibernate Distinct + order by criteria coderanch.com |
19. Problem with StatelessSession and Distinct_root forum.hibernate.orgHello, I want to query my results via StatelessSession and the following code does not ensure distinct root entity... Each entity are as much available as rows are available for it. (JOIN FETCH used in query) org.hibernate.Query query= session.statelessSession.createQuery( queryStr ).setResultTransformer( Criteria.DISTINCT_ROOT_ENTITY ) ; businessObjects = query.list().iterator(); Question : How can I set an join fetch query to distinct root entity ... |
20. Is it possible to get Table objects with a distinct clause? forum.hibernate.org |
21. How to use distinct with multiple columns in Criteria API forum.hibernate.org |
22. JPA 2.0 criteria.setResultTransformer(Criteria.DISTINCT_ROOT forum.hibernate.orgHi, What is the alternative in JPA2.0 for the following line? criteria.setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY); I have a JPA2.0 Criteria, and i need to distinct my root entities. How can I do that? Code: CriteriaBuilder builder = getEntityManager().getCriteriaBuilder(); CriteriaQuery |
23. Criteria Distinct forum.hibernate.org |
24. Distinct root is not distinct forum.hibernate.orgHi there, I'm having issues with getting proper query results using DISTINCT_ROOT_ENTITY. My query is as following: detachedCriteria.add(Restrictions.in(idField, ids)); detachedCriteria.setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY); List objects = getHibernateTemplate().findByCriteria(detachedCriteria); Reason for using the Restrictions.in construction is that I want to be able to limit the number of root objects. The query above is part of a larger retrieve() function. The issue here is that the objects ... |
25. criteria - how to inject "distinct"into the query ? forum.hibernate.orgHey, I have a criteria query I want to speed up. I currently use a "criteria.setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY);" to perform a distinct on my results, but in the sql query the distinct doesn't appear : Hibernate filters the results after the query to make sure there is no duplicate data... Is there a way to inject a "distinct" into the query with criteria ... |
26. count( distinct ... ) in HQL forum.hibernate.org |
27. DISTINCT query not working forum.hibernate.orgpublic List |
28. why does hibernate hql distinct cause an sql distinct on lef forum.hibernate.orgsession.createQuery("select distinct o from Order o left join fetch o.lineItems").list(); It looks like you are using the SQL DISTINCT keyword here. Of course, this is not SQL, this is HQL. This distinct is just a shortcut for the result transformer, in this case. Yes, in other cases an HQL distinct will translate straight into a SQL DISTINCT. Not in this case: ... |
29. sum(distinct property) in HQL forum.hibernate.org |
30. Distinct Count across multiple columns forum.hibernate.org |
31. distinct in Criteria forum.hibernate.orgI have a Parent object which has many children. Using HQL I can do a simple query to find parents which have children of a specified age like so: "select parent from Parent as parent join Children as child where child.age=10" I think I just learned today that if a parent has two children age 10 (twins) then I get the ... |
32. select distinct throws execption forum.hibernate.orgHi there! I'm trying to run the following query: select distinct technician from Technician as technician join technician.workingDays workingDays where workingDays.salesorganisation = :so order by technician.lastName, technician.technicianNo If i call the query.iterate i get the following JDBC exception: net.sf.hibernate.JDBCException: Could not execute query: ORDER BY items must appear in the select list if SELECT DISTINCT is specified. I tried to get ... |
33. distinct in Hibernate ? forum.hibernate.orgnet.sf.hibernate.sql.QuerySelect qs = new net.sf.hibernate.sql.QuerySelect(new net.sf.hibernate.dialect.OracleDialect()); qs.setDistinct(true); qs.addSelectColumn("device.deviceName", ""); qs.addSelectColumn("device.deviceDesc", ""); qs.addSelectColumn("user.firstName", ""); String qu = qs.toQueryString() + " Devic as ... |
34. Most efficient way to get distinct classes forum.hibernate.orgHi, I have a query like "from TimeSheet as timeSheet left join fetch timeSheet.timeEntry " + "where timeSheet.employee.department.id=:id " + "and timeSheet.employee.approver.approverLevel.level < :approval " + "and timeSheet.date = :date " + "order by timeSheet.employee.id asc" But this will return an entry for every time entry, even though I just want to initialize the timeEntry collection in timeSheet. Is there either ... |
35. how to find count on distinct object forum.hibernate.orgHi, I have a named query like this. SELECT distinct new com.radian.axiom.billing.cuw.bo.dto.BillingProfileSummaryDTO( summaryDTO.billingProfileID,summaryDTO.billingProfileDisplayID, summaryDTO.customerID) FROM com.radian.axiom.billing.cuw.bo.dto.BillingProfileSummaryDTO AS summaryDTO WHERE ((:customerNameFlag = 0) OR (UPPER(summaryDTO.customerName) LIKE UPPER(:customerName))) AND ((:branchNameFlag = 0) OR (UPPER(summaryDTO.coveredBranchName) LIKE UPPER(:branchName))) here i need to find the count of these distinct new records. Let me know the solution if any body knows. I am using oracle 9i and ... |
36. HQL Distinct behaviour forum.hibernate.orgHibernate descriptor: Code: |
37. Using distinct and order by forum.hibernate.orgNewbie Joined: Thu Dec 11, 2003 7:20 pm Posts: 4 I'm using Hibernate 2.1.2 with Oracle 8.1.7. I'm having a issue with using the distinct keyword along with order by. I want to select only distinct Event objects because they can have multiple types and would be listed twice in the query. The query works fine as long as I do ... |
38. Distinct in criteria queries forum.hibernate.org |
39. How to implement Orderby and distinct keywords in HQL forum.hibernate.orgDear All, Iam using hibernate2.0. My problem is like, when Iam trying to use 'Distinct and Orderby' key words in my query using Hibernate2.0 giving exception. my query as follows: select distinct new QuirkVO(qvo.quirkID,qvo.title,qvo.description) from com.phoenixcolor.pcs.vo.quirk.QuirkVO as qvo , com.phoenixcolor.pcs.vo.quirk.AccountQuirkVO as aqvo, com.phoenixcolor.pcs.vo.quirk.QuirkAccountListVO qalvo where qvo.quirkID = aqvo.quirkID and aqvo.accountID = qalvo.accountID and lower(qalvo.account) like '%a%' order by lower(qvo.title) The Exception ... |
40. Distinct problem forum.hibernate.orgHi, I have tried to do a distinct query in Hibernate but it still keeps giving me duplicates. My query is SELECT distinct ROLENAME, ROLEID FROM A_TABLE All I want is the ROLENAME to be distinct but it keeps giving me duplicate role names. This is supposed to be a very simple query. I even tried a native SQL query with ... |
41. Can I write count(distinct po.compositeId) in hql? forum.hibernate.orgnet.sf.hibernate.QueryException: path expression ends in a composite value: collection1_.id [ select count(distinct collectionPO.id) from gov.ntcri.domain.po.collection.CollectionCreatorPO as creatorPO join creatorPO.collectionPO as collectionPO where creatorPO.creatorName like '%%'and collectionPO.isDeleted=0] at net.sf.hibernate.hql.PathExpressionParser.getWhereColumn(PathExpressionParser.java:375) at net.sf.hibernate.hql.SelectParser.token(SelectParser.java:160) at net.sf.hibernate.hql.ClauseParser.token(ClauseParser.java:87) at net.sf.hibernate.hql.ClauseParser.end(ClauseParser.java:114) at net.sf.hibernate.hql.PreprocessingParser.end(PreprocessingParser.java:143) at net.sf.hibernate.hql.ParserHelper.parse(ParserHelper.java:30) at net.sf.hibernate.hql.QueryTranslator.compile(QueryTranslator.java:149) at net.sf.hibernate.hql.QueryTranslator.compile(QueryTranslator.java:138) at net.sf.hibernate.impl.SessionFactoryImpl.getQuery(SessionFactoryImpl.java:293) at net.sf.hibernate.impl.SessionImpl.getQueries(SessionImpl.java:1561) at net.sf.hibernate.impl.SessionImpl.find(SessionImpl.java:1532) at net.sf.hibernate.impl.QueryImpl.list(QueryImpl.java:39) at com.dsii.common.hibernate.HibernateUtils.getCount(HibernateUtils.java:297) at gov.ntcri.domain.dao.collection.CollectionDAO.searchByCollectionCreatorName(CollectionDAO.java:301) at gov.ntcri.domain.dao.collection.CollectionDAO.main(CollectionDAO.java:311) |
42. Criteria + order distinct forum.hibernate.orgHi all, I only want to know something about distinct with criteria. I saw that an implementation of distinct might be developped for Criteria and I want to know if it's released in the last version of Hibernate? Sorry if this question is already answered but I only found old post about this subject. Thanks in advance. kind regards. |
43. When distinct will be available in Criteria? forum.hibernate.org |
44. distinct selection problem! forum.hibernate.org |
45. Why DISTINCT doesnt't work forum.hibernate.orgFull stack trace of any exception that occurs: net.sf.hibernate.QueryException: could not resolve property: currentstatus of: cas.system.app.model.Service [select distinct s.currentstatus from cas.system.app.model.Service s where service_id > 1] at net.sf.hibernate.persister.AbstractPropertyMapping.toType(AbstractPropertyMapping.java:38) at net.sf.hibernate.hql.PathExpressionParser.getPropertyType(PathExpressionParser.java:249) at net.sf.hibernate.hql.PathExpressionParser.token(PathExpressionParser.java:123) at net.sf.hibernate.hql.SelectPathExpressionParser.end(SelectPathExpressionParser.java:12) at net.sf.hibernate.hql.ParserHelper.parse(ParserHelper.java:30) at net.sf.hibernate.hql.SelectParser.token(SelectParser.java:170) at net.sf.hibernate.hql.ClauseParser.token(ClauseParser.java:87) at net.sf.hibernate.hql.ClauseParser.end(ClauseParser.java:114) at net.sf.hibernate.hql.PreprocessingParser.end(PreprocessingParser.java:143) at net.sf.hibernate.hql.ParserHelper.parse(ParserHelper.java:30) at net.sf.hibernate.hql.QueryTranslator.compile(QueryTranslator.java:149) at net.sf.hibernate.hql.QueryTranslator.compile(QueryTranslator.java:138) at net.sf.hibernate.impl.SessionFactoryImpl.getQuery(SessionFactoryImpl.java:293) at net.sf.hibernate.impl.SessionImpl.getQueries(SessionImpl.java:1561) at net.sf.hibernate.impl.SessionImpl.find(SessionImpl.java:1532) at net.sf.hibernate.impl.QueryImpl.list(QueryImpl.java:39) |
46. Criteria distinct forum.hibernate.org |
47. Select a distinct class in a criteria query? forum.hibernate.org |
48. Select distinct forum.hibernate.org |
49. count distinct a, b? forum.hibernate.org |
50. Problem with Select Distinct query forum.hibernate.orgIs it possible to do a Select Distinct on a computed column using HQL ? I have the following perfectly valid SQL statement : SELECT DISTINCT zp.zipCode / 100 from ZipCodes zp Where zp.referenceId = 200; I try the same thing in as HQL : SELECT DISTINCT zp.zipCode / 100 from ZipCodes as zp Where zp.referenceId = 200; and I get ... |
51. SQLSERVER: ORDER BY items must appear inlist w/ DISTINCT forum.hibernate.orghi, hibernate 3.0 sql server 2k windows 2k3s, jdk 1.4.2_05 i'm using criteria to build my queries and it was working great until i did an "in" Restriction on a mapped Set and started getting dupes. From this, I decided to add a distinct Projection. When I did that, I got this query, which looks correct: select distinct this_.content_id as y0_ ... |
52. distinct and order by forum.hibernate.orgHi, I am using Hibernate 3. I have a complex query that looks like that: select setting from Device device inner join device.supportedSettings setting inner join setting.settingType type where device.manufacturer = 2 and type = 1 order by setting.name" The problem is that I am getting duplicate results. I put the distinct (select distinct setting from). However I get an exception ... |
53. distinct and order by Problem forum.hibernate.orgHi All, I am facing problem when try to execute the following query and I am using Jboss 4.0. Here is the query.. Code: public static List getProtocolConditions(long protocolID) { String hql = "select distinct ps.conditionType from ConditionsProtocolCondition " + " as ps where ps.protocol=:protocolID order by ps.conditionType"; ... |
54. Distinct and Order by Problem forum.hibernate.orgHI ALL, I am getting the following error when I am trying to execute the distinct and order by query. I am getting "not a SELECTed expression" error, since I am selecting the column which iam using in distint clause, why any suggestions would appreciated?? Hibernate version: Hibernate 3.0 Full stack trace of any exception that occurs: 14:44:27,934 WARN [JDBCExceptionReporter] SQL ... |
55. Distinct On in Hibernate forum.hibernate.org |
56. HQL distinct on a collection property forum.hibernate.orgHibernate version: 3 Name and version of the database you are using: Oracle 10g I have a class Cat with a collection of Kitten objects. I'm trying to perform a select on Cat with a distinct on Cat.kittens.id. The idea is that my HQL will return only the first Cat object that contains a specific kitten. For instance: Table: CAT id ... |
57. distinct by property forum.hibernate.orgHello, I have object with some properties: name, id, category, etc... I want to get all objects but I don't want to get two or more objects with same name property. So, first thing that crossed my mind is distinct, but when using distinct I don't really get my objects, but Strings. my hql looks like this: Criteria cr = session.createCriteria("hr.klopa.domain.FoodItem").setProjection(Projections.distinct(Projections.property("name"))); ... |
58. Problem when using count with distinct forum.hibernate.orgIf your database or JDBC driver supports this, you could use ScrollableResults. This does not actually iterate over the whole result set when you request the last page and get the row number. I think this will allow you to run the distinct query and get the size of the result set without costing too much time and without mapping the ... |
59. Hibernate TX v. DB TX: same or distinct? forum.hibernate.orgWell, let's focus on non-JTA env, i.e. using JDBCTransactionFactory. Still, the question remains: what is that JDBCTransaction's mapping to underlying DB transaction (as viewed by MySQL server). Here's the reason for my question: -MySQL 5.0 server defaults to autocommit after every SQL statement, and apparently, disabling that complicates (or makes impossible) DB clustering. -Hibernate, on the other hand, defaults to false ... |
60. select count(distinct col1, col2) from ... forum.hibernate.orgHibernate version: 3.1.2 I'm trying to find a way to generate the following query using Criteria or HQL: SELECT count(distinct col1, col2) FROM ... I get the parsing exception below when I try this in HQL. It appears that only one column is allowed in the distinct. So you are not allowed to specify multiple columns or concatenations of columns. Can ... |
61. About Distinct Query?? forum.hibernate.orgHi all, I'm newbie on Hibenate and my problem is; I have 2 main tables(TerminalTable,AirlineTable) and a sub table(TerminalAirlineBaseTable). I want to use this sub table distincted with the select query as to airline. Which criteria code should I write for resultList? TerminalAirlineBaseTable terminalID AirlineID ---------- --------- 1 2 1 2 1 2 2 4 2 4 2 4 . . ... |
62. "select distinct" objects with joined tables forum.hibernate.orghi, if I have two tables, Structures and Employes |
63. How do I count distinct columns in Hibernate 3.2? forum.hibernate.orgHi there, how do I do the following SQL in Hibernate 3.2 using Criteria and ProjectionList? I keep getting the wrong rowCount, I'm expecting 5 and I get 21. Here is the SQL I want to emulate in Hibernate: SELECT count(distinct order_id, tower_no, pallet_layer_no) from stackedproduct; My Hibernate code looks like this: stackedProductsCriteria.setProjection(Projections.projectionList() .add(Projections.rowCount(), "noOfPallets") .add(Projections.countDistinct("sortedOrder")) .add(Projections.countDistinct("towerNo")) .add(Projections.countDistinct("palletLayerNo"))); List |
64. Distinct and Projections forum.hibernate.orgMy apologies. Yes. That is the class I'm attempting to use. Version: 3.0.1 I don't have the API for this version and was trying to use the 3.2.1 version API to solve the issue. Evidently, the Projections.distinct method is newer than 3.0.1. Business is hesitant to upgrade to 3.2 until we can determine the impact on other projects so I'm needing ... |
65. How to manage shared objects in two DB with distinct hosts forum.hibernate.org |
66. Getting Distinct Count of a Projection forum.hibernate.orgThanks for pointer, but CountProjection accepts a property, not a Projection. We're looking to get the count of a Projection. I'm worried this isn't possible because it's a "Bad Idea", but I can't imagine why. So I need help making the choice one way or the other - if it can be done, how? If not, why not? Thanks for your ... |
67. cannot get distinct results for query forum.hibernate.org |
68. count (distinct (coalesce (A.x, A.y))) forum.hibernate.org |
69. How to use the Distinct interface with Criteria queries forum.hibernate.org |
70. HQL: how to write "select distinct" query forum.hibernate.orgHi, I want to write a "select distinct" query in HQL. my query has a lot of joins, so i have a lot of duplicates in the results - and this cause a performace issue. The idea of the query is as follows (see the query below): I have two entities "A" and "B". "A" has a reference to "B" (Many-to-Many). ... |
71. Hql distinct? forum.hibernate.orgHi! I have Parent object, which has a collection of Child objects. Child has an attribute attr1. I need those Parent objects, which has a Child that has an attr specified by a parameter. My try: String query = "from Parent r where r in (select a.parent from Child a where a.attr1=:attr1)"; The problem is that this query can return the ... |
72. count(distinct nvl(a,b)) problem on HQL forum.hibernate.org |
73. Derby: distinct results in error forum.hibernate.orgHibernate: select distinct user0_.ID as ID10_, user0_.EMAIL as EMAIL10_, user0_.FIRSTNAME as FIRSTNAME10_, user0_.LASTNAME as LASTNAME10_, user0_.ROLE as ROLE10_, user0_.USERNAME as USERNAME10_ from XY.XUSER user0_ where user0_.ROLE=? order by user0_.LASTNAME 2008-04-07 15:56:25,937 WARN [http-8080-2] org.hibernate.util.JDBCExceptionReporter: SQL Error: -1, SQLState: 42879 2008-04-07 15:56:25,937 ERROR [http-8080-2] org.hibernate.util.JDBCExceptionReporter: The ORDER BY clause may not contain column 'LASTNAME', since the query specifies DISTINCT and ... |
74. how to make a distinct forum.hibernate.orgHi everybody. I'm a new user of hibernate and jsp and i have this problem: I Have this method in my DAOBean class: public Set listScheda() throws Exception { try { Set |
75. sum( distinct ...) in hql forum.hibernate.org |
76. Having count(distinct a,b,c,... forum.hibernate.orgHello. I want to realize a statement in HQL or Criteria which works in SQL. The statement returns all mediator(id)s which have more than 250 orders. A order is a combination of the order date (co.created) and the id of a indent or a contract. I don't know the exactly SQL code now but this pseudo code should explain what i ... |
77. distinct selection forum.hibernate.orgHi, I have two tables and I'd like to query the last 20 comments, this works: session.createQuery("from Comments order by cm_date desc").setMaxResults(20).list() I'd like to make sure that there is no Comments from the same User in the last 20, any idea? Thanks, Angelo public class Comments { private Long id; private User user; private Timestamp cm_date; } public class User ... |
78. select distinct on multiple properties not working in HQL forum.hibernate.orgOk - I gave up on the HQL since the distinct element in it is only partially functional. I can get it to kind of work using a raw SQL query that looks like this: Code: session.createSQLQuery( " select count(distinct Month(i.REPORTED_DATE), i.PERSON) as count, " + " Month(i.REPORTED_DATE) as theMonth, " + ... |
79. [HQL]count and distinct on multiple columns forum.hibernate.orgHi, on SQL i can do sth like SELECT COUNT (*) FROM (SELECT DISTINCT column1, column2 FROM table1) to get the number of results for a distinct query. On HQL it is not allowed to use subqueries in the from-clause. I there any way i can "decorate" my query with a count or use distinct and count at the same time ... |
80. Hibernate query using DISTINCT forum.hibernate.orgIs it possible to find a unique records based on a single filed. eg: select distinct on (md.STRING01) md.STRING01, md.DATE01 , md.LONG01, md.LONG02, md.LONG03,md.LONG04 from app.MENU md Can you pls suggest an equivalent hibernate query? When tried distinct it checks distinct on all fileds. I want to check only one filed and return the latest one when duplictes occurs. |