Solved

Reset row_number on field change

Posted on 2015-01-23
8
412 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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 143

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
Moving data to the cloud? Find out if you’re ready

Before moving to the cloud, it is important to carefully define your db needs, plan for the migration & understand prod. environment. This wp explains how to define what you need from a cloud provider, plan for the migration & what putting a cloud solution into practice entails.

 
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

Moving data to the cloud? Find out if you’re ready

Before moving to the cloud, it is important to carefully define your db needs, plan for the migration & understand prod. environment. This wp explains how to define what you need from a cloud provider, plan for the migration & what putting a cloud solution into practice entails.

Question has a verified solution.

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

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
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
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

621 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