Concept Search Generates non-standard SQL

The HibernateConceptDAO uses Criteria API for searching. It uses the DISTINCT, GROUP BY and ORDER BY clauses. The getConcepts method generates non-standard SQL that runs fine on MySQL, but does not work with other databases like PostgreSQL, SQL Server and Oracle.

1.) The GROUP BY query in standard SQL requires Single-Value Rule, i.e. every column in the select-list must either be named in the GROUP BY clause or else be an argument to a set function. The concept search uses only one property in the grouping and this produces non-standard SQL

2.) the ORDER BY clause requires that property for ordering should be defined in the select list, where as in the concept search we are using "weight" in ORDER BY and using "max(weight)" in SELECT. Thus, in the ORDER BY, we need to use "max(weight)" as well.