EXPERT RESPONSE
This is one of three questions this week, to which, I'm afraid, the answer is no.
[The others were Entity-Attribute-Value constraints and SQL to update the header records?]
No, this is not possible, not with the information you've given. You've overlooked the need to have a sequencing column. But once we resolve this, the rest is easy.
Somewhere in every basic SQL tutorial should be the idea that there is no sequence in database tables. The rows could be placed higgledy-piggledy anywhere on the disk drive, and not in any particular sequence, but in "disorderly and utterly irregular fashion."
Even if you use a clustered index (search this term for more information; then ask yourself what column would you declare the clustered index on), you are not actually guaranteed that the database will actually store rows in clustered index sequence. The database might have an occasional hiccough and need to stick the row elsewhere. Sequence in SQL is guaranteed only when you use the ORDER BY clause, or, alternatively, when you use a comparison operator such as "less than" on the distinct values in a sequencing column. Incrementing numbers make good sequences, and gaps are fine; the numbers do not need to be consecutive because allowing for gaps is easier than assuring that there are none. Timestamps are also okay, unless you get multiple invoices per millisecond.
Once you have settled on a sequencing column, the rest is easy. Do a LEFT OUTER JOIN from each row to the previous one. This is a self join. It has to be a LEFT OUTER JOIN because you want to include the first row returned in sequence, and the first row doesn't have a previous row. How do you know which one is the previous one? It's the one with the highest sequence value that is less than the sequence value of this one. So the join condition would be something like:
from invoices as this
left outer
join invoices as prev
on prev.seq =
( select max(seq)
from invoices
where seq < this.seq )
Neat, eh?
|
