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.