Posted by Ed Murphy on 09/22/07 03:48

steve wrote:

> On Sep 20, 7:10 pm, Ed Murphy <emurph...@socal.rr.com> wrote:
>> .
>> As I noted the other day, dense rank stopped being a good example
>> when DENSE_RANK() became a 2K5 built-in.

[example snipped]
> We want the dense rank of val where the order of id is meaningful.

This is not the dense rank of either val or id, but rather a running
total. In this particular case, it's a running total of the number
of times val changes value (in either direction) when the rows are
ordered by id. I can't think of a real-world case where I would
want to do this, but there are plenty of real-world cases where some
other type of running total is desired. And I would not want to use
your product to produce a running total; I find the syntax severely
non-intuitive.

Crystal Reports does running totals in a straightforward fashion,
along the lines of:

Field to total = SalesOrderLine.TotalCost
Type of total = Sum
Add to total = Each record
Reset total = On change of SalesOrderHeader.SalesOrderNumber

(Probably several other reporting-layer packages as well; Crystal is
just the one I'm familiar with.)

I might also watch for a future version of SQL Server to include
RUNNING_TOTAL() OVER(ORDER BY <column(s)>), similar to ROW_NUMBER()
which is effectively a special case of a running total:

Field to total = fixed value 1
Type of total = Sum
Add to total = Each record
Reset total = Never; or, if PARTITION BY <column(s)> is used,
then on change of those column(s)

>> Suggest you come up with a different example.
>
> That's a good idea. There are a gazillion of them:) Perhaps you'd like
> to suggest one(s).

That's your job. While your product has some other useful features
(e.g. error messages that report not only a constraint that would be
violated, but also a row that would violate that constraint), I
remain unconvinced that table abstraction is one of them.

[Back to original message]