Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

HOW TO ASSIGN CONSECUTIVE NUMBERS IN SQL

Posted on 2012-04-12
5
Medium Priority
?
630 Views
Last Modified: 2012-06-27
I have a SQL Server table with the following fields:
ID
PRODUCT
ORIGINATION DATE
It contains several different ID's, each with up to 40 PRODUCTs. I need to order the products by ORIGINATION DATE, and then assign a new field with the consecutive number. For example, source data:
ID 1
PRODUCT A
ORIGINATION DATE MARCH 2, 2012
ID 1
PRODUCT B
ORIGINATION DATE DECEMBER 10, 2011
ID 1
PRODUCTO C
ORIGINATION DATE DECEMBER 14, 2011
I need the following output:
ID 1
PRODUCT A
ORIGINATION DATE MARCH 2, 2012
CONSECUTIVE NUMBER: 3
ID 1
PRODUCT B
ORIGINATION DATE DECEMBER 10, 2011
CONSECUTIVE NUMBER: 1
ID 1
PRODUCTO C
ORIGINATION DATE DECEMBER 14, 2011
CONSECUTIVE NUMBER: 2
0
Comment
Question by:CASH59
[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
  • 3
  • 2
5 Comments
 
LVL 39

Expert Comment

by:appari
ID: 37840660
try this
Select  ID, PRODUCT, [ORIGINATION DATE] , Row_numer() over(order by [ORIGINATION DATE]) [CONSECUTIVE NUMBER]
from tableName
0
 
LVL 39

Expert Comment

by:appari
ID: 37840661
and if you want to reset the number within each ID try this

Select  ID, PRODUCT, [ORIGINATION DATE] , Row_numer() over(partition by ID order by [ORIGINATION DATE]) [CONSECUTIVE NUMBER]
from tableName
0
 

Author Comment

by:CASH59
ID: 37840677
I need to create the CONSECUTIVE NUMBER first, it would be a new field.
thanks
0
 
LVL 39

Accepted Solution

by:
appari earned 2000 total points
ID: 37840702
then try this,

to Add new column execute the following script
ALTER TABLE tableName ADD
	[CONSECUTIVE NUMBER] int NULL

Open in new window


To update the data execute the following script

;with a as (
Select  ID, PRODUCT, [ORIGINATION DATE] , Row_numer() over(partition by ID order by [ORIGINATION DATE]) CON_NUM
from tableName)
Update a set [CONSECUTIVE NUMBER] =CON_NUM

Open in new window

0
 

Author Comment

by:CASH59
ID: 37840746
It works. Thanks
0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
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…

636 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