sql - Collapsing resultset based on group by - Oracle -

sql - Collapsing resultset based on group by - Oracle -

input records in txn_ctry table:

txn_id ben_code ben_ctry ben_score orig_code orig_ctry orig_score 1 usa 1 zz unknown -1 2 ca canada 1 pr portugal 2.3 2 de federal republic of germany 1.5 mx united mexican states 8.5 2 fr french republic 2.5 zz unknown -1 3 aa anon -2 usa 1 3 ca canada 1 aa anon -2 3 usa 1 aa anon -2

output required:

txn_id ben_code ben_score orig_code orig_score 1 1 zz -1 2 fr 2.5 mx 8.5 3 ca 1 1

requirement find highest ben_score , orig_score per txn_id shown in output.

as shown in sample input info (more rows per txn_id can exist,but info snippet), txn_id = 1 has 1 row , hence straight forward.

txn_id = 2 has 3 rows , highest ben_score ben_code = fr , highest orig_score orig_code = mx

in case of tie between codes within txnid, code ascending alphabetical order needs selected case txn_id = 3 ben_code values of , ca have tie same high score of 1, ca should picked based on alphabetical order of corresponding ctry column (ben_ctry , orig_ctry).

i appreciate solutions desired output in single query.

this query implement requirements:

with enhanced_data ( select data.txn_id, first_value(ben_code ) on ( partition txn_id order ben_score desc, ben_code asc) first_ben_code, first_value(ben_score ) on ( partition txn_id order ben_score desc, ben_code asc) first_ben_score, first_value(orig_code ) on ( partition txn_id order orig_score desc, orig_code asc) first_orig_code, first_value(orig_score) on ( partition txn_id order orig_score desc, orig_code asc) first_orig_score info ) select distinct txn_id, first_ben_code, first_ben_score, first_orig_code, first_orig_score enhanced_data order txn_id

see sqlfiddle here.

i suspect you'll need proper indexing, if these big info sets.

sql oracle oracle11g