programming forums Java Java JSRs Mobile Certification Databases Caching Books Engineering OS Languages Paradigms IDEs Build Tools Frameworks Products This Site Careers Other all forums
this forum made possible by our volunteer staff, including ...
Marshals:
Sheriffs:
Saloon Keepers:
Bartenders:

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

drac yang
Ranch Hand
Posts: 78
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
Sheriff
Posts: 11338
177
And what's the expected output if you have a 5th record like:

id name value group
5 a3 1 a

Dave Tolls
Ranch Foreman
Posts: 2996
37
What is the ORDER BY which defines what row is 'first'?

K. Holgate
Greenhorn
Posts: 3

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

drac yang
Ranch Hand
Posts: 78
K. Holgate wrote:

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

thanks, how can I fulfill this by hibernate criteria?

drac yang
Ranch Hand
Posts: 78
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
Ranch Hand
Posts: 78
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
Ranch Foreman
Posts: 2996
37
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
Ranch Hand
Posts: 78
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.