A lot of sql users have a hard time reconciling the idea that a table
is by
definition an unordered set and the ordering involved in the sql
server 2005 ranking functions. This article attempts to fill in the
missing pieces of explanation that sql seems to have OVERlooked. The
article takes the view that knowledge of the functions does not
consist of just 'how' to use them. Perhaps it's possible to squeeze in
'what' they really are, where they came from. As far as a relational
database can realizes them.

http://beyondsql.blogspot.com/2008/0...-overture.html

steve wrote:

> A lot of sql users have a hard time reconciling the idea that a table
> is by
> definition an unordered set and the ordering involved in the sql
> server 2005 ranking functions. This article attempts to fill in the
> missing pieces of explanation that sql seems to have OVERlooked. The
> article takes the view that knowledge of the functions does not
> consist of just 'how' to use them. Perhaps it's possible to squeeze in
> 'what' they really are, where they came from. As far as a relational
> database can realizes them.
>
> http://beyondsql.blogspot.com/2008/0...-overture.html

Rank is a primary concept. Shoehorning it into the concept of a cursor
is like shoehorning NOT IN into NOT EXISTS; just because you can doesn't
mean you should; you should have a good reason to add that complexity.

In the case of NOT IN vs. NOT EXISTS, the usual good reason is when you
need to look at multiple columns. (ISTR there's also some weirdness
pertaining to NULLs.) In the case of rank versus a cursor, your "here
is something that the rank functions can't handle" example is this:

> id val
> ---- ----
> 1 a
> 2 a
> 3 a
> 5 a
> 7 b
> 9 b
> 11 a
> 13 a
> 17 b
> 19 b
> 23 b
> 29 a
> 31 b
> 37 b
>
> How do you get a dense rank for val in the order of id? The solution would look
> like this:
>
> id val DenseRank
> ---- ---- ---------
> 1 a 1
> 2 a 1
> 3 a 1
> 5 a 1 | change 1
> 7 b 2 |
> 9 b 2 | change 2
> 11 a 3 |
> 13 a 3 | change 3
> 17 b 4 |
> 19 b 4
> 23 b 4 | change 4
> 29 a 5 |
> 31 b 6 | change 5
> 37 b 6 |
>
> In this case the dense rank is certainly not based on distinct values of val. It's
> based on the number of changes between the values of val, ie. from 'a->b' or 'b->a'
> in the direction of id (which is ascending).

But this is arguably not a rank; it's an understandable concept, but it
deserves a different name. More to the point, it deserves a practical
example in which you would actually want to do something like this; I
can't think of one offhand.

On Apr 20, 3:49 pm, Ed Murphy <emurph...@socal.rr.com> wrote:

> Rank is a primary concept. Shoehorning it into the concept of a cursor
> is like shoehorning NOT IN into NOT EXISTS; just because you can doesn't
> mean you should; you should have a good reason to add that complexity.
>

Thanks for your comments.

Given the idea that Rank is a primary concept I'm suggesting that it's
understandable based on more primitive ones. One of which a cursor
(type). It's where the order in the ranking comes from. Rather than
shoehorning it in it seems to be a case of - if the shoe fits wear
it I'm not sure I understand your suggestion of introducing
complexity. . The idea of a cursor seems very basic. One of the themes
of a relational database is to simplify understanding. That's what I
was trying to do though I'm not sure I succeeded Behind every
function is an idea. Ranking is no exception.

> But this is arguably not a rank; it's an understandable concept, but it
> deserves a different name. More to the point, it deserves a practical
> example in which you would actually want to do something like this; I
> can't think of one offhand.

I'm taking the position that a rose is a rose is a rose. Because the
data doesn't fit within the parameters of the dense rank function is
that reason to call it something else?
You may find this post interesting on the subject:

microsoft.public.sqlserver.programming
Oct 30 2007
'Count Occurances in Select Statement'
http://www.themssforum.com/SQLServer...rances-726289/

steve wrote:

> Given the idea that Rank is a primary concept I'm suggesting that it's
> understandable based on more primitive ones. One of which a cursor
> (type). It's where the order in the ranking comes from. Rather than
> shoehorning it in it seems to be a case of - if the shoe fits wear
> it I'm not sure I understand your suggestion of introducing
> complexity. . The idea of a cursor seems very basic. One of the themes
> of a relational database is to simplify understanding. That's what I
> was trying to do though I'm not sure I succeeded Behind every
> function is an idea. Ranking is no exception.

Replacing this:

select c1, c2, rank() over (order by c1) r
from t
order by c1, c2

with this:

select ToTable(
ToList(
cursor(t order by c1)
)
)
order by c1, c2

is hardly a simplification.

>> But this is arguably not a rank; it's an understandable concept, but it
>> deserves a different name. More to the point, it deserves a practical
>> example in which you would actually want to do something like this; I
>> can't think of one offhand.
>
> I'm taking the position that a rose is a rose is a rose. Because the
> data doesn't fit within the parameters of the dense rank function is
> that reason to call it something else?

I suppose it's a rank over something that isn't a simple ORDER BY. I
still can't think of a practical situation where you'd actually want
this, though, and this:

> You may find this post interesting on the subject:
>
> microsoft.public.sqlserver.programming
> Oct 30 2007
> 'Count Occurances in Select Statement'
> http://www.themssforum.com/SQLServer...rances-726289/

is an equally artificial example and thus doesn't help.

Assuming that such a practical situation does exist and I just don't
know that it is (i.e. that Josh is not a sock puppet of yours), I
would still prefer to see it expressed without explicit reference to
a cursor:

select c1, c2, c3, rank() over (order by c1, change(c2)) r
from t
order by c1, c2, c3

Some of your ideas (this one among them) strike me as analogous to
CISC, which has been largely superseded by RISC for a reason: making
common simple cases harder in return for making uncommon complex cases
easier is generally not a net win.

On Apr 20, 9:22 pm, Ed Murphy <emurph...@socal.rr.com> wrote:
>.
> Replacing this:
>
> select c1, c2, rank() over (order by c1) r
> from t
> order by c1, c2
>
> with this:
>
> select ToTable(
> ToList(
> cursor(t order by c1)
> )
> )
> order by c1, c2
>
> is hardly a simplification.
>

It's not meant as a simplification but as a explanation of what being
done by rank(). As an aid to understanding, a visual aid if you will.
If you're using dataphor and an sql db for storage I'm not suggesting
you not use the sql rank(). I use rank() in an sql pass-through
query because it's easy and fast. Understanding<>code.

> > You may find this post interesting on the subject:
>
> > microsoft.public.sqlserver.programming
> > Oct 30 2007
> > 'Count Occurances in Select Statement'
> >http://www.themssforum.com/SQLServer...rances-726289/
>
> is an equally artificial example and thus doesn't help.
>
> Assuming that such a practical situation does exist and I just don't
> know that it is (i.e. that Josh is not a sock puppet of yours), I
> would still prefer to see it expressed without explicit reference to
> a cursor:
>.

I would have to say that " (i.e. that Josh is not a sock puppet of
yours)" is an example of me running into Murphy's Law

Hi!
I have been very happy for the analytic function in Oracle-SQL (PL/
SQL),
so I really miss the full support for them in TSQL
/Bjorn

On Apr 23, 6:42 am, B D Jensen <bjorn.d.jen...@gmail.com> wrote:
> Hi!
> I have been very happy for the analytic function in Oracle-SQL (PL/
> SQL),
> so I really miss the full support for them in TSQL
> /Bjorn

Hello,

Well that is a very different issue than what I was talking about MS
doesn't have 'full' support of them because they make decisions about
what they add to Sql Server based on different criteria than Oracle
and DB2. MS has had over 8 years to add full support of analytic
functions and they haven't. That should tell you something Many
times MS marches to a different tune than its competitors

Let me add that 'all' analytic functions can be understood in the
framework I suggested. But I understand that for many knowing what is
behind them is irrelevant. It is only necessary to know how to use
them. Perhaps sql flourishes best with the uncluttered mind. But I'm
always holding out hope that there are those who are exceptions

www.beyondsql.blogspot.com