How to select rows by max value from another column in Oracle

Question

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

Answer 1

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;