Post reply

How to generate sequence number based two columns

  • August 18, 2016 at 3:08 am

    #312625

    Kindly help me to create sequence number for based on 2 colums [cycleno,name] ,

    I have data now like #tmp1 , I need output as #tmp2 with sequence number refer "sNo" column

    create table #tmp1(cycleno int ,name varchar(3))

    insert into #tmp1(cycleno,name)

    values (1,'YUS'),(1,'YUS'),(1,'YUS'),(2,'YUS'),(2,'YUS'),(3,'YUS'),(3,'YUS'),(1,'SDF'),(1,'SDF'),(2,'SDF'),(3,'SDF'),(4,'SDF'),(4,'SDF')

    select * from #tmp1

    create table #tmp2(sNo int ,cycleno int ,name varchar(3))

    insert into #tmp2(sNo,cycleno,name)

    values (1,1,'YUS'),(1,1,'YUS'),(1,1,'YUS'),(2,2,'YUS'),(2,2,'YUS'),(3,3,'YUS'),(3,3,'YUS'),

    (4,1,'SDF'),(4,1,'SDF'),(5,2,'SDF'),(6,3,'SDF'),(7,4,'SDF'),(7,4,'SDF')

    select * from #tmp2

    From #tmp1 Name "YUS" cycle starts from 1,2,3 same like another name "SDF" cycle starts from 1,2,3 ,4 .

    I want to generate sequence number like #tmp2 table sNo column, 1,2,3,4,5,6,7...

    Thanks in advance.

  • August 18, 2016 at 3:55 am

    #1896096

    SELECT

    sNo = DENSE_RANK() OVER(ORDER BY [name] DESC, cycleno),

    cycleno, [name]

    FROM #tmp1

    ORDER BY [name] DESC, cycleno

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply