Hibernate - cant use GROUP BY With ORDER BY together

This one is odd...

I have an hql query that ignore the GOUP BY if ORDER BY is included.

it will perform the query without the group by,

but if I remove the order by it will work fine

  list = getSession().createQuery(
        "SELECT 
  Brand.name as Brand_name 
, Brand.url as Brand_url 
, Brand.email as Brand_email 
, Brand.brandId as Brand_brandId 
, Brand.description as Brand_description 
FROM com.affiliates.hibernate.Brand Brand  
INNER JOIN Brand.users as users 
WHERE 1=1  
AND users.userId>'0' 
order by  Brand.email ASC 
group by brandId"//this one will be ign开发者_C百科ored because of the order by
    ).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP).list();

sql generated:

select
        brand0_.NAME as col_0_0_,
        brand0_.URL as col_1_0_,
        brand0_.EMAIL as col_2_0_,
        brand0_.DESCRIPTION as col_3_0_ 
    from
        BRAND brand0_ 
    inner join
        USERS_BRANDS users1_ 
            on brand0_.BRAND_ID=users1_.BRAND_ID 
    inner join
        USER user2_ 
            on users1_.USER_ID=user2_.USER_ID 
    where
        1=1 
        and user2_.USER_ID>'0' 
    order by
        brand0_.EMAIL ASC limit ?       

---

First, notice that BRAND.BRAND_ID has been drop from the column projections in the SQL. This is probably related to the group by being dropped as well.

Second, notice there are no aggregate functions defined in the query. Group only works on aggregations. Try adding an aggregate function, such as max, to all of the columns. This might be the cause of the problem

Last, try fully qualifying brandId in the HQL to eliminate any confusion:

group by Brand.brandId

---

You should apply orderby after groupby but you have written as

order by  Brand.email ASC 
group by brandId"//this one will be ignored because of the order by
    ).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP).list();

But Change to:

group by brandId"//this one will be ignored because of the order by
    ).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP).list();
    order by  Brand.email ASC 

Then it will work