Consider two tables:
book(id, title, date_written , author_id)
author(id, name)
the date_written field is a DATE field ad rappresents the date the book
was written.
i would like to make a query like this:
SELECT name , title , date_written
FROM book INNER JOIN author ON ( book.author_id = author.id )
ORDER BY author.id , time_written
the result would be
King , Running Man , 1978-9-12
King , Salem's Lot , 1980-3-29
[...]
Shakespeare, Romeo and Juliet , 1214-6-13
Shakespeare , Amlet , 1216-11-6
Shakespeare , ... , 1226-5-22
[...]
but I would like to add a column with the "sequence number" of the book,
related to the author, like
King , Running Man , 1 , 1978-9-12
King , Salem's Lot , 2 , 1980-3-29
[...]
Shakespeare, 1 , Romeo and Juliet , 1214-6-13
Shakespeare , 2 , Amlet , 1216-11-6
Shakespeare , 3 , ... , 1226-5-22
[...]
what query must I issue?
can anyone help me?
tia
--
Mattia Cazzola