Solved

Reset row_number on field change

Posted on 2015-01-23
8
299 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
ID: 40566157
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
ID: 40566158
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
ID: 40566160
this small addition will do;
row_number() over (partition by order_no order [by product]
0
NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

 
LVL 1

Author Comment

by:takwirirar
ID: 40566163
Excellent!
0
 
LVL 1

Author Comment

by:takwirirar
ID: 40566168
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
ID: 40566172
I have requested the question to be re-opened.
0
 
LVL 1

Author Closing Comment

by:takwirirar
ID: 40566269
Thank you all!
0

Featured Post

Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

772 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