SQL: Different ways to generate sequence

There was a question from the op regarding adding a new column to a query output by generating the cyclic sequence numbers from 1 to 3.

Select A =identity(int,1,1),B,C from table_abs

1,A,41

2,B,13

3,C,90

4,D,91

5,E,98

6,F,12

7,G,54

8,H,16

For this output, the 4th column generates the Sequence of numbers from 1 to 3 which is shown below

1,A,41,1

2,B,13,2

3,C,90,3

4,D,91,1

5,E,98,2

6,F,12,3

7,G,54,1

8,H,16 ,2

If you are using SQL 2012 then Sequence would be natural choice for any such operations.

Solution 1

Using Sequence

CREATE SEQUENCE Seq
 AS INTEGER
 START WITH 1
 INCREMENT BY 1
 MINVALUE 1
 MAXVALUE 3
 CYCLE;

SELECT table_name,NEXT VALUE FOR Seq New_column
FROM information_schema.tables

 

Solution 2

Using CTE and Modulus operator

;with q as
(
  select row_number() over (order by (select null)) A, *
  from sys.objects
)
select A, 1+A%3 B, *
from q

Solution 3

Loops and Temp table

create table dummyTest
(
id int, 
col1 char(1),
col2 int
)

insert into dummyTest values(1,'A',410),(2,'B',411),(3,'c',4111),(4,'d',421),(5,'e',441),(6,'f',451),(7,'g',481),(8,'h',401)

create table #dummy
(
id int, 
col1 char(1),
col2 int,
NewColumn int
)
 declare @n int,@i int,@limit int
 set @limit=1
 set @i=1
 select @n=count(*) from dummyTest
 while @i<=@n
 begin
 set @limit=1
 while @limit<=3
 begin
 print @limit
 insert into #dummy
 select *,NewColumn=@limit from dummyTest where id=@i
 set @i=@i+1
 set @limit=@limit+1
 end
  end

 select * from #dummy

The simplest of all the above methods

create table dummyTest
(
id int,
col1 char(1),
col2 int
)

insert into dummyTest values(1,’A’,410),(2,’B’,411),(3,’c’,4111),(4,’d’,421),(5,’e’,441),(6,’f’,451),(7,’g’,481),(8,’h’,401)

select id,col1,col2, (row_number() over (order by id)-1) %4+1 as cycle from dummyTest

 

 

Conclusion

The same solution can be derived using the cursor and there may be other solutions as well. At many instances, we opt for any solutions without thinking of data volume that may degrade the performance. This is one of the prime examples of why we need to upgrade to newer version.

 

 

 

Advertisements

About Prashanth Jayaram

DB Technologist, Author, Blogger, Operations-Mgr at CTS, Automation Expert, Technet WIKI Ninja, MVB and Powershell Geek You can connect me via https://social.technet.microsoft.com/profile/prashanth jayaram/ http://www.sqlshack.com/author/prashanth/ https://www.tumblr.com/blog/prashantjayaram http://www.sqlservercentral.com/blogs/powersql-by-prashanth-jayaram/ http://www.toadworld.com/members/prashanthjayaram/ My Articles are published in following sites http://www.ssas-info.com/analysis-services-articles/ http://db-pub.com/ http://www.sswug.org/sswugresearch/community/
This entry was posted in SQL, SQL 2012, Uncategorized and tagged , , . Bookmark the permalink.

2 Responses to SQL: Different ways to generate sequence

  1. Vic says:

    Just curious where the…
    Select A =identity(int,1,1),B,C from table_abs
    .. came from? It certainly will fail on SQL Server with:
    The IDENTITY function can only be used when the SELECT statement has an INTO clause.
    Thanks

  2. Christian says:

    Nice one!
    But, given the tables in the question a simple case-statement with %3 would have been the simples solution.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s