selecting only those records with the largest value in a column

[Bob Grossman]

Consider this Oracle database table with this data:

create table responses_v5 ( 
     user_id varchar2(20), 
     hw_id number(38), 
     pb_id number(38), 
     tries number(38), 
     grade number(3,2), 
     response varchar2(20), 
     primary key(user_id, hw_id, pb_id, tries) );
insert into responses_v5 values ( 'bob', 4540, 1, 1, 0.0, 'response1' );
insert into responses_v5 values ( 'bob', 4540, 1, 2, 0.0, 'response2' );
insert into responses_v5 values ( 'bob', 4540, 1, 3, 1.0, 'response3' );
insert into responses_v5 values ( 'refoyl', 4540, 1, 1, 0.0, 'response4' );
insert into responses_v5 values ( 'refoyl', 4540, 1, 2, 0.8, 'response5' );
insert into responses_v5 values ( 'refoyl', 4540, 2, 1, 0.0, 'response6' );
insert into responses_v5 values ( 'refoyl', 4540, 2, 2, 0.5, 'response7' );
insert into responses_v5 values ( 'refoyl', 4540, 2, 3, 1.0, 'response8' );
insert into responses_v5 values ( 'refoyl', 4541, 1, 1, 0.5, 'response9' );
insert into responses_v5 values ( 'refoyl', 4541, 1, 2, 0.0, 'response0' );

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:

'bob', 4540, 1, 3, 1.0, 'response3'
'refoyl', 4540, 1, 2, 0.8, 'response5'
'refoyl', 4540, 2, 3, 1.0, 'response8'
'refoyl', 4541, 1, 2, 0.0, 'response0'

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]

You could employ analytics here:
select * from (select t.*, row_number() over (partition by user_id, hw_id, pb_id order by tries desc) r from responses_v5 t) where r = 1;

USER_ID              HW_ID                  PB_ID                  TRIES                  GRADE                  RESPONSE             R 
-------------------- ---------------------- ---------------------- ---------------------- ---------------------- -------------------- --
bob                  4540                   1                      3                      1                      response3            1
refoyl               4540                   1                      2                      0,8                    response5            1
refoyl               4540                   2                      3                      1                      response8            1
refoyl               4541                   1                      2                      0                      response0            1

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?