sql - Group by two columns and by max of another column -

sql - Group by two columns and by max of another column -

i need able grouping 2 columns , bring row id of column these 2 columns max value. details i'm after in 4th column called answer

column1 | column2 | numbercol | reply --------------- ---------------------------- 123 | 456 | 1 | 123 | 456 | 2 | x 123 | 456 | 3 | s 654 | 564 | 1 | 654 | 564 | 2 | s 654 | 564 | 3 | p 654 | 564 | 4 | b

so need reply s first grouping results , reply b sec grouping results

you can utilize subquery join result:

select t1.column1, t1.column2, t1.numbercol, t1.answer yourtable t1 inner bring together ( select column1, column2, max(numbercol) maxnum yourtable grouping column1, column2 ) t2 on t1.column1 = t2.column1 , t1.column2 = t2.column2 , t1.numbercol = t2.maxnum

see sql fiddle demo

sql oracle