A Simple Sequence
We are going to first create a query that gives us sequence numbers in a new column. All we need to do here is create a sequence of numbers running the entire table starting at 1 and increasing by 1 for each row. Here is some code we will run against the table. We use the expression row_number() over(order by tblTable). This post is part of a small series of posts on sequential numbers.
SELECT firstname ,item ,ROW_NUMBER() OVER(order by firstname) as SeqNum FROM people
Here are the results.
Suppose we need to permanently store this data into our table [people]. We need to create a new column in the table and then run an update query. Here is how we create a new column using T-SQL.
ALTER TABLE people ADD SeqNum int;
Now we need to run an UPDATE command to update our new column with our sequence numbers we will create with the ROW-NUMBER() OVER(order by …). How do we do that in T-SQL?
To do this we will be using common table expressions (CTE). They use the WITH keyword. Phillip Burton over at the online Udemy.com in his 6-part course on Querying SQL Server “loves” CTE’s because they make our SQL statements so much easier to read and understand. Plus, we can re-use the CTE’s we write. Here is what our query looks like.
select * from people; with s_people as ( select * , row_number() over(order by firstname) as seq from people ) update s_people set SeqNum = seq select * from people;
Here are our successful results.
That worked great. There is at least two other ways to do this. One way is to use a sub-query, however, using CTEs is more clear. We won’t look at the sub-query code here. We will however look at something quite different in the next section.
Right in the middle of this discussion we’ll toss in something completely different, based on an article at MSSQL Tips.
DECLARE @id INT SET @id = 0 UPDATE people SET @id = SeqNum = @id + 1 SELECT * FROM people;
Here are the successful results in SSMS.
ROW_NUMBER OVER PARTITION
You may have a need to create sequential numbers for duplicates. You may have a column in a table that you need to create duplicate sequential numbers in another column that differentiates the duplicates. How do you do that? If you search the internet for “sql create sequence numbers for duplicates” or something like that you will find an article at StackOverflow.com.
For more information on OVER(), have a look at the another post called SQL Server Over() Partition By.
I modified one of the posts in that article to produce the following script that you can run on a non-production server. This is actually more simple that the one in the post. I like to start off as simple as possible.
begin tran create table people ( firstname varchar(15), item varchar(5) ) insert into people values('Bob',1) insert into people values('Bob',1) insert into people values('Bob',1) insert into people values('Jill',1) insert into people values('Sally',1) insert into people values('Sally',1) select * from people SELECT ROW_NUMBER() OVER(PARTITION by firstname,item ORDER BY firstname ) AS duplicates,firstname,item FROM people rollback tran
Here is the result in SSMS.
Duplicates Defined over Multiple Columns
Now we will run a query.
SELECT ROW_NUMBER() OVER(PARTITION by firstname,lastname ORDER BY firstname, lastname ) AS duplicates,firstname,lastname,item FROM People02 /* The WHERE duplicates does not work. SQL Server says duplicates is invalid name. How do we get around this? SELECT ROW_NUMBER() OVER(PARTITION by firstname,lastname ORDER BY firstname, lastname ) AS duplicates,firstname,lastname,item FROM People02 WHERE duplicates > 1 */
Here are the query results. Notice that in the case of “Bob” we were able to define multiple columns and distinguish between “Bob Smith” and “Bob Johnson” even when the last names were in separate columns.