Solved

# Number rows based on change in field

Posted on 2012-08-30
Medium Priority
377 Views
I have a query which is pulling records and I need to change the Client_id to an incrementing INT number when the value of a row changes.  Example, I have multplie contacts for a customer being returned and when the client_id (which is the same for all the contacts changes) I want to increment the value in a column by 1 like shown.

Dont care if this is done in SQL or EXCEL.  Just needed one time

Example

NewID      Client_   ContaNumber         name
5000      00100000001rlgsAAA      1      CNet
5001      00100000001rlgtAAA      1      VeriSign, Inc.
5002      00100000001tre3AAA      1      21st Century Scientific
5002      00100000001tre3AAA      2      21st Century Scientific
5003      00100000001tre4AAA      1      3 V Co.
5003      00100000001tre4AAA      2      3 V Co.
5004      00100000001tre5AAA      1      3-K Machinery Company Inc.
5004      00100000001tre5AAA      2      3-K Machinery Company Inc.
0
Question by:red_75116

LVL 22

Assisted Solution

Flyster earned 668 total points
ID: 38352495
Assuming NewID is column A and Cliebt in column B, in A3 paste this formula:

=IF(B4=B3,A3,A3+1)

Copy down as far as you need.

Flyster
0

LVL 4

Accepted Solution

jekautz earned 668 total points
ID: 38352507
Excel has a way of auto-incrementing data in a column when you select two or more cells that exhibit an incrementing pattern.  First select some cells, then grab the control node in the lower-right corner of the selection rectangle and drag it down.

Here are some examples:
http://www.symplebyte.com/microsoft_office/excel/auto_increment.html
0

LVL 39

Assisted Solution

appari earned 664 total points
ID: 38352688
what is the version of sqlserver you are using?
if you are using sqlserver 2005 or above you can use "Row_number() over(Partition by fieldnames order by orderfieldnames)" function in your sql to generate auto increment numbers.
post the table structure and indicate the fields on which you want to check for the change and the columns to use in ordering.
0

Author Closing Comment

ID: 38479406
thanks for the excel suggestions as that worked quick. I plan to try this with SQL next time it comes up.
0

## Featured Post

Question has a verified solution.

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

This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…
###### Suggested Courses
Course of the Month16 days, 14 hours left to enroll