Solved

Reset row_number on field change

Posted on 2015-01-23
8
269 Views
Last Modified: 2015-01-23
Hi Experts, given the query attached and results, how do I get it to reset the row_number() every time the order_no changes?

So instead of the row_number() / line_number continually incrementing, I want it to go 1,2,3 then 1,2,3,4,5 depending on the lines in the order

Current Query   Results
0
Comment
Question by:takwirirar
8 Comments
 
LVL 24

Accepted Solution

by:
Phillip Burton earned 180 total points
Comment Utility
Don't know exactly what you mean, but try changing:

SELECT row_number() over (ORDER BY [product])

to

SELECT row_number() over (PARTITION BY [product] ORDER BY [order_no])
0
 
LVL 14

Assisted Solution

by:nishant joshi
nishant joshi earned 160 total points
Comment Utility
You need to use partition by to reset row number.

SELECT ROW_NUMBER() OVER(PARTITION BY Order_No ORDER BY [Product]) as line_number,Order_no,product
,order_qty,CONVERT(VARCHAR(10),data_required,120) as date_required FROM t

Open in new window

0
 
LVL 142

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 160 total points
Comment Utility
this small addition will do;
row_number() over (partition by order_no order [by product]
0
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 
LVL 1

Author Comment

by:takwirirar
Comment Utility
Excellent!
0
 
LVL 1

Author Comment

by:takwirirar
Comment Utility
Apologies moderator, I meant to give all experts a share of points as all answers are correct. How do I do this?
0
 
LVL 24

Expert Comment

by:Phillip Burton
Comment Utility
I have requested the question to be re-opened.
0
 
LVL 1

Author Closing Comment

by:takwirirar
Comment Utility
Thank you all!
0

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now