Hi Benny,
This is what the spec says about order by options:
When the ORDER BY clause is used in a query, each element of the SELECT clause
of the query must be one of the following:
1. an identification variable x, optionally denoted as OBJECT(x)
2. a single_valued_association_path_expression
3. a state_field_path_expression
Regards,
-marina
glassfish_at_javadesktop.org wrote:
> Hi All,
>
> I am running into a problem trying to order my query result by an aggregated column. Here is my query :
>
> SELECT a.standardID, COUNT(a.standardID) FROM XStandardMOEntity a GROUP BY a.standardID ORDER BY COUNT(a.standardID)
>
> Exception Description: Syntax error parsing the query [SELECT a.standardID, COUNT(a.standardID) FROM XStandardMOEntity a GROUP BY a.standardID ORDER BY COUNT(a.standardID)], line 1, column 98: syntax error at [COUNT].
> Internal Exception: line 1:98: expecting IDENT, found 'COUNT'
> at oracle.toplink.essentials.exceptions.EJBQLException.syntaxErrorAt(EJBQLException.java:379)
> at oracle.toplink.essentials.internal.parsing.ejbql.EJBQLParser.handleANTLRException(EJBQLParser.java:335)
> at oracle.toplink.essentials.internal.parsing.ejbql.EJBQLParser.addError(EJBQLParser.java:278)
> at oracle.toplink.essentials.internal.parsing.ejbql.EJBQLParser.reportError(EJBQLParser.java:378)
> at oracle.toplink.essentials.internal.parsing.ejbql.antlr273.EJBQLParser.variableAccess(EJBQLParser.java:1441)
> at oracle.toplink.essentials.internal.parsing.ejbql.antlr273.EJBQLParser.pathExpression(EJBQLParser.java:977)
> at oracle.toplink.essentials.internal.parsing.ejbql.antlr273.EJBQLParser.stateFieldPathExpression(EJBQLParser.java:1512)
> at oracle.toplink.essentials.internal.parsing.ejbql.antlr273.EJBQLParser.orderByItem(EJBQLParser.java:4620)
> at oracle.toplink.essentials.internal.parsing.ejbql.antlr273.EJBQLParser.orderByClause(EJBQLParser.java:618)
> at oracle.toplink.essentials.internal.parsing.ejbql.antlr273.EJBQLParser.selectStatement(EJBQLParser.java:241)
> at oracle.toplink.essentials.internal.parsing.ejbql.antlr273.EJBQLParser.document(EJBQLParser.java:135)
> at oracle.toplink.essentials.internal.parsing.ejbql.EJBQLParser.parse(EJBQLParser.java:166)
> at oracle.toplink.essentials.internal.parsing.ejbql.EJBQLParser.buildParseTree(EJBQLParser.java:127)
> at oracle.toplink.essentials.internal.ejb.cmp3.base.EJBQueryImpl.buildEJBQLDatabaseQuery(EJBQueryImpl.java:215)
> at oracle.toplink.essentials.internal.ejb.cmp3.base.EJBQueryImpl.buildEJBQLDatabaseQuery(EJBQueryImpl.java:189)
> at oracle.toplink.essentials.internal.ejb.cmp3.base.EJBQueryImpl.buildEJBQLDatabaseQuery(EJBQueryImpl.java:153)
> at oracle.toplink.essentials.internal.ejb.cmp3.base.EJBQueryImpl.<init>(EJBQueryImpl.java:114)
> at oracle.toplink.essentials.internal.ejb.cmp3.base.EJBQueryImpl.<init>(EJBQueryImpl.java:99)
> at oracle.toplink.essentials.internal.ejb.cmp3.EJBQueryImpl.<init>(EJBQueryImpl.java:86)
> at oracle.toplink.essentials.internal.ejb.cmp3.EntityManagerImpl.createQuery(EntityManagerImpl.java:204)
> ... 43 more
> Caused by: line 1:98: expecting IDENT, found 'COUNT'
> at persistence.antlr.Parser.match(Parser.java:212)
> at oracle.toplink.essentials.internal.parsing.ejbql.antlr273.EJBQLParser.variableAccess(EJBQLParser.java:1434)
> ... 58 more
>
> From reading the several docs that I have suggested that I should be able to do the above query. But since all the documentation only shows/explain how to do ORDER BY and AGGREGATE seperately I wasn't able to find an example where both are applied at the same time. Please let me know if I am missing anything.
>
> Thanks in advance,
> Benny
> [Message sent by forum member 'blackrose12' (blackrose12)]
>
> http://forums.java.net/jive/thread.jspa?messageID=237404
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: users-unsubscribe_at_glassfish.dev.java.net
> For additional commands, e-mail: users-help_at_glassfish.dev.java.net
>