RANK function used over a partion - unexpected result

Question

RANK function used over a partion - unexpected result

itlk

Mr or Mrs. 500

Points: 578
More actions
March 5, 2013 at 7:33 pm

#274877

Hi,
I have the following table / data:
idTitleDirector
===================================
1movidir1
2modir2
3movie333dir333
4moviedir4
5moviedir4
6moviedir4
If I run the statement:
select id, title, director, RANK() over (partition by title order by (select 0)) as RNK
from Movies;
I get the following result:
idTitleDirectorRNK
===========================================
2modir2 1
1movidir1 1
4moviedir4 1
5moviedir4 1
6moviedir4 1
3movie333 dir333 1
I would expect the RNK value for id =4,5,6 would be 1,2,3
Why are the values for id = 4,5,6 identical?
Thank you for help.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

Because of the ORDER BY (select 0) in the over clause. The RANK is being calculated based on 0 for each record, and since they are the same each row is given the rank of 1.

Look at the code below and see if you can understand what is happening. I have added an extra record to your data.

--idTitleDirector

--===================================

--1movidir1

--2modir2

--3movie333dir333

--4moviedir4

--5moviedir4

--6moviedir4

create table #Movies(

id int,

title varchar(16),

director varchar(16)

);

insert into #Movies

values (1,'movi','dir1'),

(2,'mo','dir2'),

(3,'movie333','dir333'),

(4,'movie','dir4'),

(5,'movie','dir4'),

(6,'movie','dir4'),

(7,'movie','dir5');

select

id,

title,

director,

RANK() over (partition by title order by (select 0)) as RNK

from

#Movies;

select

id,

title,

director,

RANK() over (partition by title order by director) as RNK

from

#Movies;

select

id,

title,

director,

RANK() over (partition by title order by id) as RNK

from

#Movies;

drop table #Movies;

itlk Mr or Mrs. 500 Points: 578 More actions · Answer 2

I see. I thought that the rank is calculated based on column(s) in a partition and not in the 'order by' clause.

Thank you for help.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 3

itlk (3/5/2013)
I see. I thought that the rank is calculated based on column(s) in a partition and not in the 'order by' clause.
Thank you for help.

I think you are thinking of ROW_NUMBER().