I have two datasets in Oracle Table1 and Table2. When I run this:
SELECT A.ID, B.NUM_X FROM TABLE1 A LEFT JOIN TABLE2 B ON A.ID=B.ID WHERE B.BOOK = 1 It returns this.
ID NUM_X 1 10 1 5 1 9 2 2 2 1 3 20 3 11 What I want are the DISTINCT ID where NUM_X is the MAX value, something like this:
ID NUM_x 1 10 2 2 3 20 You can use aggregation:
SELECT A.ID, MAX(B.NUM_X) FROM TABLE1 A LEFT JOIN TABLE2 B ON A.ID = B.ID WHERE B.BOOK = 1 GROUP BY A.ID; If you wanted additional columns, I would recommend window functions:
SELECT A.ID, MAX(B.NUM_X) FROM TABLE1 A LEFT JOIN (SELECT B.*, ROW_NUMBER() OVER (PARTITION BY ID ORDER BY NUM_X DESC) as seqnum FROM TABLE2 B ) B ON A.ID = B.ID AND B.seqnum = 1 WHERE B.BOOK = 1 GROUP BY A.ID;