How to assign correlative numbers to rows only using SQL?

Question

I have the following table in an Oracle database:

InvoiceNumber InvoiceDate InvoiceCorrelative
------------- ----------- ------------------
          123  02-03-2009                  0
          124  02-03-2009                  0
          125  02-04-2009                  0
          126  02-04-2009                  0
          127  02-04-2009                  0
          128  02-05-2009                  0
          129  02-06-2009                  0
          130  02-06-2009                  0
          ...         ...                ...

And I want to set a value for the InvoiceCorrelative column in every row in order to have a sequence of numbers starting at 1 for each date. In the example above I want the table to look like this:

InvoiceNumber InvoiceDate InvoiceCorrelative
------------- ----------- ------------------
          123  02-03-2009                  1
          124  02-03-2009                  2
          125  02-04-2009                  1
          126  02-04-2009                  2
          127  02-04-2009                  3
          128  02-05-2009                  1
          129  02-06-2009                  1
          130  02-06-2009                  2
          ...         ...                ...

Is it possible to do it only using SQL statements?. I've been playing with rownum but didn't get anywhere.

Answer 1

Try:

ROW_NUMBER() OVER (PARTITION BY InvoiceDate ORDER BY InvoiceNumber)

Answer 2

Using Standard SQL,

  Update TabkleName T Set
    InvoiceCorrelative = 
     (Select Count(*) From TableName 
      Where InvoiceDate = T.InvoiceDate
        And InvoiceNumber <= T.InvoiceNumber)