Remove duplicates with least row ids from Oracle

I have a database table which looks like

ID	Book_no	Book_name	Book_category
ID1	1	B1	CB1
ID1	2	B1	CB1
ID1	3	B2	CB1
ID1	4	B2	CB1
ID1	5	B3	CB1
ID2	1	B1	CB2
ID2	2	B1	CB2
ID2	3	B2	CB2

And the expected result is like

ID	Book_No	Book_name	Book_category
ID1	2	B1	CB1
ID1	4	B2	CB1
ID1	5	B3	CB1
ID2	2	B1	CB2
ID2	3	B2	CB2

I want to delete duplicate records from table on the basis of ID, Book_name and Book_category. Below query deletes the duplicate records, but the result is not expected one. As I want to delete all the duplicate records except the highest Book_no. Want to maintain the highest Book_no and delete all other duplicates.

Answer

You can DELETE correlating on the ROWID pseudo-column:

DELETE FROM table_name
WHERE ROWID IN (
  SELECT rid
  FROM   (
    SELECT ROWID AS rid,
           ROW_NUMBER() OVER (
             PARTITION BY id, book_name, book_category
             ORDER BY book_no DESC
           ) AS rn
    FROM table_name
  )
  WHERE  rn > 1
);

Which, for the sample data:

CREATE TABLE table_name (id, book_no, book_name, book_category) AS
SELECT 'ID1', 1, 'B1', 'CB1' FROM DUAL UNION ALL
SELECT 'ID1', 2, 'B1', 'CB1' FROM DUAL UNION ALL
SELECT 'ID1', 3, 'B2', 'CB1' FROM DUAL UNION ALL
SELECT 'ID1', 4, 'B2', 'CB1' FROM DUAL UNION ALL
SELECT 'ID1', 5, 'B3', 'CB1' FROM DUAL UNION ALL
SELECT 'ID2', 1, 'B1', 'CB2' FROM DUAL UNION ALL
SELECT 'ID2', 2, 'B1', 'CB2' FROM DUAL UNION ALL
SELECT 'ID2', 3, 'B2', 'CB2' FROM DUAL;

Then the remaining rows are:

SELECT * FROM table_name;

ID	BOOK_NO	BOOK_NAME	BOOK_CATEGORY
ID1	2	B1	CB1
ID1	4	B2	CB1
ID1	5	B3	CB1
ID2	2	B1	CB2
ID2	3	B2	CB2

sqlfiddle here