Wrong "row_number" behaviour

Question

Wrong "row_number" behaviour

Bhuvnesh

SSC Guru

Points: 59351
More actions
December 14, 2010 at 5:40 am

#244320

create table #tmpChoice
(
choice_seqn int IDENTITY(1,1),
choice nvarchar(1000)
)
insert into #tmpChoice
select 'Less than 1 year'
union all select '1-2 years'
union all select '3-5 years'
union all select '1-2 years'
union all select '3-5 years'
union all select '6-10 years'
union all select 'More than 10 years'
select * from #tmpChoice
select * from (
select choice_seqn,choice, row_number() over(partition by choice order by choice_seqn ) AS row_id from #tmpChoice )t
where t.row_id = 1
select choice_seqn,choice, row_number() over(partition by choice order by choice_seqn ) AS row_id from #tmpChoice
drop table #tmpChoice
You can see the second results shows abnormal choice_seqn selection for row_id =1.
but my observation is,for row_id =1 choice_seqn should be for 1 to 5.
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

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

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

davidandrews13 SSCertifiable Points: 6855 More actions · Answer 1

i would think the over(partition by choice order by choice_seqn ) is going to ORDER BY Choice_sqn within each Choice that you have partitioned by. Since each Choice only seems to have one item, the ORDER BY doesn't really get used.

Intsead, it is ordering by Choice.

you would have to put order by choice_seqn at the end, so

select choice_seqn,choice, row_number() over(partition by choice order by choice_seqn ) AS row_id from #tmpChoice order by choice_seqn

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 2

The results are exactly as expected from these trivial statements.

PARTITION BY choice results in 5 rows returned from the first query with dupes eliminated from two of them.

What were you expecting to see? What output would you like?

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 3

Thanks i got the point

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)