0 Replies Latest reply on Mar 2, 2007 2:59 AM by Henning Eiben

    order-by in named-query

    Henning Eiben Newbie

      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 :(