How to assign correlative numbers to rows only using SQL?
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.
Try:
ROW_NUMBER() OVER (PARTITION BY InvoiceDate ORDER BY InvoiceNumber)
Using Standard SQL,
Update TableName T Set
InvoiceCorrelative =
(Select Count(*) From TableName
Where InvoiceDate = T.InvoiceDate
And InvoiceNumber <= T.InvoiceNumber)
精彩评论