Super Function II

When tables are 'variables' you can create what I called
a 'Super Function'. Pass any table of the appropriate 'type'
to the function, it's that simple. The example was a function
that returned a dense rank including when the target column
of the rank repeats. The function assumed a single primary key.
And only the PK and the target column of the rank constituted
the table to be passed to the function.
http://beyondsql.blogspot.com/2007/08/dataphor-creating-supe r-function.html

Well it's a simple matter to overload the function for tables that
have a compound primary key. And you can envision a library of
different functions based on table 'type'.
http://beyondsql.blogspot.com/2007/09/dataphor-super-functio n-ii.html

Hopefully some will 'get' it :-)

www.beyondsql.blogspot.com

Re: Super Function II

steve wrote:

> When tables are 'variables' you can create what I called
> a 'Super Function'. Pass any table of the appropriate 'type'
> to the function, it's that simple. The example was a function
> that returned a dense rank

As I noted the other day, dense rank stopped being a good example
when DENSE_RANK() became a 2K5 built-in. Suggest you come up
with a different example.

Re: Super Function II

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

Hello Ed,

Consider this table in a sql server 2005 database.

create table TG1
(
id int not null primary key,
val varchar(10) not null
);

insert into TG1(id, val) values( 1, 'a');
insert into TG1(id, val) values( 2, 'a');
insert into TG1(id, val) values( 3, 'a');
insert into TG1(id, val) values( 5, 'a');
insert into TG1(id, val) values( 7, 'b');
insert into TG1(id, val) values( 9, 'b');
insert into TG1(id, val) values(11, 'a');
insert into TG1(id, val) values(13, 'a');
insert into TG1(id, val) values(17, 'b');
insert into TG1(id, val) values(19, 'b');
insert into TG1(id, val) values(23, 'b');
insert into TG1(id, val) values(29, 'a');
insert into TG1(id, val) values(31, 'b');
insert into TG1(id, val) values(37, 'b');

We want the dense rank of val where the order of id is meaningful.
In this case the values of val 'repeat' within the primary key (id).

Using the DenseRank function from:
http://beyondsql.blogspot.com/2007/08/dataphor-creating-supe r-function.html

Here is a unique set of dense ranks:

select
DenseRank(TG1 {id PK,val Grp}) {PK id,DenseRank}
join
TG1
{id,val,DenseRank}
order by {id} ;

id val DenseRank
-- --- ---------
1 a 1
2 a 1
3 a 1
5 a 1
7 b 2
9 b 2
11 a 3
13 a 3
17 b 4
19 b 4
23 b 4
29 a 5
31 b 6
37 b 6

Now use the S2005 dense_rank() function.

select id,val,dense_rank() over(order by val) as DenseRank
from TG1
order by id

id val DenseRank
----------- ---------- --------------------
1 a 1
2 a 1
3 a 1
5 a 1
7 b 2
9 b 2
11 a 1
13 a 1
17 b 2
19 b 2
23 b 2
29 a 1
31 b 2
37 b 2

In this example the sql server dense_rank function, by itself, cannot
derive unique values of the dense rank.It can only derive (by itself)
a dense rank where the target column (val) does *NOT* repeat within a
meaningful order of another column(s), ie. pk column id.

In my article:
http://beyondsql.blogspot.com/2007/09/dataphor-super-functio n-ii.html

I mention this problem of repeating values of the target of the rank:

Quote
This being due to the colossal shortsightedness of sql of not
separating the target of the rank and the ordering of it. We should be
able to order the target of the rank by any column(s) we choose. All
sql ranking functions treat the target of the rank and the order of
the target of the rank as the same column(s). See:
http://beyondsql.blogspot.com/2006/09/rac-rank-this.html
for more details.
CloseQuote

(In this article there are other links about this 'repeating' dense
rank
problem if your interested),

All the DenseRank operators I wrote will obtain a dense rank
regardless of whether the target of the rank repeats its values or
not.

I hope this explains things about the dense rank example :-)

>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).

I know you have other questions from another post. I'll try to get to
them. I'm preparing a bunch of other articles on kewl stuff.
Unfortunately it's just me and there is just so much to do and
seemingly so little time.
Now if I only had a staff :-)

I appreciate your interest and your patience.

best,
steve

www.beyondsql.blogspot.com
www.rac4sql.net

Re: Super Function II

steve wrote:

> On Sep 20, 7:10 pm, Ed Murphy 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 ), 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 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.

Re: Super Function II

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

Nope, not likely. The ANSI/ISO syntax is already there and I hope SQL
Server would use it:

SUM(foobar)
OVER ([ORDER BY ..]
[PARTITION BY ..]
)

The defines how many preceding or following rows are in
the window (that is the name of the OVER() clause. You have a wide
range of options here. Look it up; DB2 and Oracle have full
implementations.