create an auto-incrementing result column in oracle

Question

I'm trying to produce the following results: emp_no, book_no, book_id Where emp_no and book_id are the actual columns on the table, but book_no is a value that is incremental, for example:

emp_no,book_no,book_id
1234,01,AB33
1234,02,GF44
5678,01,HH78
5678,02,EE98
5678,03,JJ03

Is it possible to do this in a standard query without defining variables beforehand (like in sqlplus), and if so please could you give me an example.

Answer 1

SELECT  emp_no,
        ROW_NUMBER() OVER (PARTITION BY emp_no ORDER BY book_id) AS book_no,
        book_id
FROM    books