Row_number() not working when distinct the query.

Question

5.00/5 (1 vote)

See more:

Hi,

Row_number() not working when distinct the query.

For Example,

I have 400 Records in my table. In that Records some duplicates are avaliable.

When i distinct i got only 4 Rows. (This is correct)

But, when i use the same query in addition to add one column "Row_number() OVER(PARTITION BY RoleCode ORDER BY ID AS [Sl.No]" distinct not working. it will show all 400 Records.

See my Query (Before Row_Number())
-----------------------------------
select distinct ID,RoleCode,projcode from tab1 where rolecode='ALE' and projcode='268'

Output
------
ID RoleCode ProjCode
1266 ALE 268
1347 ALE 268
1408 ALE 268
1420 ALE 268

Same Query After using Row_Number() --> Distinct not working and shows all values
-------------------------------
select distinct ID,RoleCode,projcode,Row_number() OVER(PARTITION BY rolecode ORDER BY ID) AS [Sl.No] from tab1 where rolecode='ALE' and projcode='268'

TranID RoleCode ProjCode Sl.No
1266 ALE 268 1
1266 ALE 268 2
1266 ALE 268 3
1266 ALE 268 4
1266 ALE 268 5
1266 ALE 268 6
1266 ALE 268 7
1266 ALE 268 8
1266 ALE 268 9
1266 ALE 268 10
1266 ALE 268 11
1266 ALE 268 12
1266 ALE 268 13
1266 ALE 268 14
1266 ALE 268 15
1266 ALE 268 16
---- --- --- --
---- --- --- --

400 Rows Affected

What is the problem and why distinct not working when using Row_number() in SQL Query...?

Posted 31-Oct-11 2:50am

gani7787

Add a Solution

Comments

Amir Mahfoozi 31-Oct-11 14:00pm

What was the result ?

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Amir Mahfoozi · Accepted Answer · 2011-10-31T03:00:00

Solution 1

Try this one :

SQL

select * , Row_number() OVER(PARTITION BY rolecode ORDER BY ID) AS [Sl.No] from
( 
   select distinct ID,RoleCode,projcode from tab1 where rolecode='ALE' and projcode='268'
) as s

Posted 31-Oct-11 3:00am

Amir Mahfoozi