Win a copy of Think Java: How to Think Like a Computer Scientist this week in the Java in General forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

selecting only those records with the largest value in a column

 
Bob Grossman
Ranch Hand
Posts: 78
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Consider this Oracle database table with this data:



I want to use SQL to select only the records that have the maximum number of tries for each unique combination of user_id, hw_id, and pb_id. Such a query would return only these records from the table above:



Currently I am acquiring all the records with SQL, ordering them by user_id, hw_id, pb_id, and –tries (so the record with the most tries is listed first), and then using Java to go through all the retrieved records, storing in a List each record that has a new combination of user_id, hw_id, and pb_id and discarding the remaining records that have the same combination of user_id, hw_id, and pb_id. But clearly it would be better if I did not acquire those extra records in the first place.

Any ideas how to structure the query so it gives me only the data I want?
 
Martin Vajsar
Sheriff
Posts: 3752
62
Chrome Netbeans IDE Oracle
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
You could employ analytics here:



Search for SQL analytics or Oracle analytics to get started with them. It is an incredibly powerful tool.

What do you want to do if there are two (or more) records with the same values of user_id, hw_id, pb_id and tries?
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic