wood burning stoves 2.0*
The moose likes JDBC and the fly likes selecting only those records with the largest value in a column Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "selecting only those records with the largest value in a column" Watch "selecting only those records with the largest value in a column" New topic
Author

selecting only those records with the largest value in a column

Bob Grossman
Ranch Hand

Joined: Dec 18, 2008
Posts: 69
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

Joined: Aug 22, 2010
Posts: 3610
    
  60

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?
 
It is sorta covered in the JavaRanch Style Guide.
 
subject: selecting only those records with the largest value in a column