[jboss-user] [EJB 3.0] - order-by in named-query

[eiben]

Hi,

I created a named query like this:

  | @NamedQuery(name = "topItems", query = "select i from Orderitem oi inner join oi.item i group by i order by sum(oi.quantity) desc")
  | 

  | public Collection<Item> getTopItems()
  | {
  | 	Query q = em.createNamedQuery("topItems");
  | 	q.setMaxResults(25);
  | 	return (Collection<Item>) q.getResultList();
  | }
  | 

But this doesn't seem to work like I expect it to work: I get an exception from the JDBC-driver :( It seems like my database (MySQL) doesn't like the "sum(oi.quantity)" in the order by clause :(

well, I played with the sql, and it seems like MySQL would prefer something like this:


  | @NamedQuery(name = "topItems", query = "select i, sum(oi.quantity) as qty from Orderitem oi inner join oi.item i group by i order by qty desc")
  | 

But this in turn doesn't seem to work with hibernate, at least I tried to evaluate this expression in the HQL-Editor, and hibernate could not create a SQL-statment :(

View the original post : http://www.jboss.com/index.html?module=bb&op=viewtopic&p=4024559#4024559

Reply to the post : http://www.jboss.com/index.html?module=bb&op=posting&mode=reply&p=4024559