how can I get every first result in each group by hibernate Criteria?

[drac yang]

how can I get every first result in each group by hibernate Criteria?

for example, there're 4 records in database
id name value group
1 a1 1 a
2 a2 2 a
3 b1 1 b
4 b2 2 b

what i wanna get is [a1, b1].
how to determine it's the first record in the group?
if its value is smallest, it's the first value in the group.

[Roel De Nijs]

And what's the expected output if you have a 5th record like:

id name value group
5 a3 1 a

[Dave Tolls]

What is the ORDER BY which defines what row is 'first'?

[K. Holgate]

SELECT name FROM table a WHERE value = (SELECT min(value) FROM table b WHERE a.group= b.group);

You could do this with a self-join if you prefer.

[drac yang]

K. Holgate wrote:SELECT name FROM table a WHERE value = (SELECT min(value) FROM table b WHERE a.group= b.group);

You could do this with a self-join if you prefer.

thanks, how can I fulfill this by hibernate criteria?

[drac yang]

Roel De Nijs wrote:And what's the expected output if you have a 5th record like:

id name value group
5 a3 1 a

yes, you are right, my provided conditions are insufficient, we can use order by.
key point here is how to fulfill this by hibernate criteria?

[drac yang]

Dave Tolls wrote:What is the ORDER BY which defines what row is 'first'?

ORDER BY value, if only we can get the first result in the group.
and the most important thing i.e. my original question is how to fulfill this requirement by hibernate criteria.

[Dave Tolls]

Using a criteria may be the key point, but until you can figure out the SQL you would right outside of a Criteria you won't get very far.

So (assuming K. Holgate's query is correct, which is something you'd need to test) you would be looking at a Subquery method, probably the eq() one.

[drac yang]

Dave Tolls wrote:Using a criteria may be the key point, but until you can figure out the SQL you would right outside of a Criteria you won't get very far.

So (assuming K. Holgate's query is correct, which is something you'd need to test) you would be looking at a Subquery method, probably the eq() one.

thank you, i'll have to check more related criteria materials.