I am using Grails Criteria (similar to hibernate criteria) to get a list of Student who got highest grade in each division from the given table. And I want ONLY Name
, Division
and Grade
fields.
Name | Division | Grade | Std_id
---------------------------------
AA1 | A | 2 | 1
AA2 | A | 4 | 2
BB1 | B | 2 | 3
BB2 | B | 5 | 4
The result I want is
Name | Division | Grade |
--------------------------
AA2 | A | 4 |
BB2 | B | 5 |
if I use the following criteria
def criteria = Student.createCriteria()
def resultlt = criteria.list {
projections {
groupProperty('divison')
max('grade')
}
}
I got ONLY Division
and Grade
, other fields are not included. I need Name
field as well.
If I changed the criteria (used aggregate functions and property together in projections) to
def criteria = Student.createCriteria()
def resultlt = criteria.list {
projections {
property('name')
groupProperty('divison')
max('grade')
}
}
It give the following error..
ERROR: column "this_.name" must appear in the GROUP BY clause or be
used in an aggregate function
Position: 63. Stacktrace follows:
org.postgresql.util.PSQLException: ERROR: column "this_.name" must
appear in the GROUP BY clause or be used in an aggregate function
Position: 63
at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryEx
ecutorImpl.java:2161)
at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutor
Impl.java:1890)
at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.ja
va:255)
at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Stat
ement.java:559)
at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(Abstract
Jdbc2Statement.java:417)
at org.postgresql.jdbc2.AbstractJdbc2Statement.executeQuery(AbstractJdbc
2Statement.java:302)
I think there is no way to get answer using createCriteria but I tried something else, Please try it:
def studentList = Student.executeQuery("from Student A where A.Grade in (select max(B.Grade) from Student as B group by B.Division)")
This is a common aggregation problem. the selected fields must appear in the GROUP BY clause[*]. As I can see your Division column and Name column combination are incomparable so you need to do it in another way. I think you need subquery for the above condition .
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With