selecting only those records with the largest value in a column
posted 4 years ago
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?