HQL Query using group by

Question

I have the following class structure FlowerDAO with the fields (mapped using Hibernate):

• id
• unitPrice
• name

• color

  How can i create an hql query to obtain the flower(s) for each color that has(ve) the minimum unit price on that color?

I have tried this, but it doesn't work

from FlowerDAO as f where f.unitPrice<= (select min(f2.unitPrice) from FlowerDAO as f2 where f2.color=f.color)
group by f.color

Answer 1

I suspect the problem is comparing a single value to a set of results. Try replacing the <= with an 'in', so your query will read:

from FlowerDAO as f 
  where f.unitPrice in 
    (select min(f2.unitPrice) from FlowerDAO as f2 where f2.color=f.color)

Also, I've removed the 'group by' from the end, since that will only provide one result for each colour, and you mention in the question flower(s). You may want to use

order by f.color

instead?