Re: [eclipselink-users] Aggregate Functions in ORDER BY

[Tom Ware <tom.ware@xxxxxxxxxx>]

Hi Derek,

  The spec says you cannot quite do what you are proposing, but offers a way to 
order by Aggregate Expressions.

  You can do something like this:

Select MAX(a.date) as maxdate from DateEntity order by maxdate.

  I have just been told there may be a bug with a query above in EclipseLink, 
but it is something we should be able to address - so feel free to file a bug if 
you have issues.

  BTW: The "as" clause ( as maxdate) is not allowed in a constructor 
expression, but you may be able to come up with a query that achieves what you want.

-Tom


Derek Knapp wrote:
> is it possible to do something like this?
>
>
>         return em.createQuery("SELECT new 
> itrac.data.WebHotlist(MAX(a.date) as maxdate, c) " +
>                               "FROM Activity a INNER JOIN a.cookieid 
> cookie " +
>                               "INNER JOIN cookie.contactidCollection c " +
>                               "INNER JOIN c.listidCollection l " +
>                               "WHERE l.userid = :user " +
>                               "AND a.companyid = :company " +
>                               "AND a.date BETWEEN :start AND :end " +
>                               "GROUP BY c " +
>                               "ORDER BY *MAX(a.date)* DESC")
>
>                           .setParameter("user", user)
>                           .setParameter("company", user.getCompanyid())
>                           .setParameter("start", start)
>                           .setParameter("end", end)
>                           .getResultList();
>
>
> if not, is it best to use a sorted collection, and make the WebHotlist 
> comparable?
>
>
> ------------------------------------------------------------------------
>
> _______________________________________________
> eclipselink-users mailing list
> eclipselink-users@xxxxxxxxxxx
> https://dev.eclipse.org/mailman/listinfo/eclipselink-users