|
|
Posted by steve on 09/21/07 09:59
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.
>.
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-super-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-function-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
Navigation:
[Reply to this message]
|