• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

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

 
drac yang
Ranch Hand
Posts: 75
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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: 10662
144
AngularJS Chrome Eclipse IDE Hibernate Java jQuery MySQL Database Spring Tomcat Server
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
And what's the expected output if you have a 5th record like:

id name value group
5 a3 1 a
 
Dave Tolls
Ranch Hand
Posts: 2091
15
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
What is the ORDER BY which defines what row is 'first'?
 
K. Holgate
Greenhorn
Posts: 3
Oracle Ruby
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator


You could do this with a self-join if you prefer.
 
drac yang
Ranch Hand
Posts: 75
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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: 75
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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: 75
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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 Hand
Posts: 2091
15
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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: 75
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic