A running total

Digg This!     StumbleUpon     Del.icio.us

RELATED CONTENT SQL How to check SQL query construction with the Mimer Validator Using the SQL GROUP BY clause for counting combinations How to use an SQL CASE expression How to sort an SQL UNION query with special ORDER BY sequence How to use string functions to make an SQL join An SQL solution for a customer order homework problem How to use SQL's POSITION function with substrings Using SQL date functions to get totals for last three days Using CASE in the SQL ORDER BY clause What's the difference between an SQL inner join and equijoin? Oracle development languages How to check SQL query construction with the Mimer Validator Understanding SQL string functions The top advice from Oracle experts in 2008 What's the difference between an SQL inner join and equijoin? Using LEFT OUTER JOIN query to get zero row counts in SQL How to return multiple values for THEN clause in an SQL CASE expression Can I concatenate row values in SQL? Should I try to avoid a LEFT OUTER JOIN in SQL? Tips for derived tables in SQL and using FULL OUTER JOINs How to write an SQL query for two foreign keys to the same table

RELATED RESOURCES 2020software.com, trial software downloads for accounting software, ERP software, CRM software and business software systems Search Bitpipe.com for the latest white papers and business webcasts Whatis.com, the online computer dictionary

This is one of three questions this week, to which, I'm afraid, the answer is no.

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?