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.

Another Way

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.