row_number() with over(partition by) gives two different output

Question

5.00/5 (1 vote)

See more:

SQL-Server-2005

when i use row_number() with over(partition by) it works fine but when i use the same query at another time i get slightly different output and the reason is order by clause in the partition by clause.

Posted 2-Oct-12 22:54pm

PratikShah2366

Add a Solution

Comments

Achha Insan 3-Oct-12 5:32am

nothing is impossible. you can do it.

Abhijit Parab 3-Oct-12 5:39am

please post your query here

PratikShah2366 3-Oct-12 5:49am

select idno = row_number() OVER(PARTITION BY cltcode ORDER BY defaultbank DESC,LEN(ACCNO) desc) into #bank from .....

3 solutions

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)

PratikShah2366 · Answer 1 · 2012-10-02T23:39:00

please let me know some alternate solution...i store the row_number values and use only top 3 values for further calculation...so each time i run the query i get different value. for eg

Bank Name rowid
----------------------------
IDBI LTD. 1
YES BANK LTD 2
IDBI LTD. 3
STATE BANK OF INDIA 4

first when i ran the query

Bank Name rowid
----------------------------
IDBI LTD. 1
IDBI LTD. 2
YES BANK LTD 3
STATE BANK OF INDIA 4

second time when i ran the query

PratikShah2366 · Answer 2 · 2012-10-02T23:41:00

Solution 2

part of query

idno = row_number() OVER(PARTITION BY cltcode ORDER BY defaultbank DESC,LEN(ACCNO) desc)

Posted 2-Oct-12 23:41pm

PratikShah2366

PratikShah2366 · Answer 3 · 2012-10-04T19:18:00

Solution 3

please any 1 can help...to find the solution

Posted 4-Oct-12 19:18pm

PratikShah2366

row_number() with over(partition by) gives two different output

3 solutions

Solution 1

Solution 2

Solution 3

Add your solution here

Preview 0

Existing Members

...or Join us