Should this work?
I have a classic table with a handful of columns (Note_ID, Note_Date, Speaker_ID, Comment). In a perfect world, I would enter the date and time into the date column, and the rows would sort by date/time. I don't always have the time available, so I've added a number column titled SEQ.
I've set the default value for the SEQ column using the code shown below, thinking that when I create a new row, it would default to the next Row_Number, but I would be able to over-ride that value during data entry if I needed to insert the row between two pre-existing rows.
However, when I create a new row, the NOTE_ID populates correctly, but the SEQ defaults to 1
What am I doing wrong? Is there a different way I should accomplish this?
Thank you -
MT in NY
select row_number()
OVER
(PARTITION BY NOTE_ID ORDER BY NOTE_DATE)
from
phys_prod_note
where note_ID = :P928_NOTE_ID