Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Reset row_number on field change

Posted on 2015-01-23
8
Medium Priority
?
574 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
7 Comments
 
LVL 24

Accepted Solution

by:
Phillip Burton earned 720 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 640 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 143

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 640 total points
ID: 40566160
this small addition will do;
row_number() over (partition by order_no order [by product]
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to shrink a transaction log file down to a reasonable size.

569 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