none
Add Sequence Number within group

    Question

  • Hi,

    I have the following data:

    POL# POL_EFF_DATE
    123 1-1-2012
    123 1-1-2012
    123 1-1-2012
    123 1-1-2013
    123 1-1-2013
    456 1-1-2012
    456 1-1-2012
    456 1-1-2013
    456 1-1-2013

    I want to add sequence number to each group with same Pol# and Effective Date like this:

    POL# POL_EFF_DATE Seq
    123 1-1-2012 1
    123 1-1-2012 1
    123 1-1-2012 1
    123 1-1-2013 2
    123 1-1-2013 2
    456 1-1-2012 1
    456 1-1-2012 1
    456 1-1-2013 2
    456 1-1-2013 2

    is there a set based function to accomplish this in SQL 2008?

    thanks
    Scott
    Monday, October 14, 2013 12:53 AM

Answers

  • Hi,

    If you want to add sequence with the group where pol is equal,we need to add partition by clause to what Eshani has given..Like.

    declare @t as table(POL int,POL_EFF_DATE date)
    insert into @t values(123,'1-1-2012'),(123,'1-1-2012'),(123,'1-1-2012'),(123,'1-1-2013')
    ,(123,'1-1-2013'),(456,'1-1-2012'),(456,'1-1-2012'),(456,'1-1-2013'),(456,'1-1-2013')
    select * from @t
    
    select POL,POL_EFF_DATE,
    DENSE_RANK() over (partition by pol order by POL_EFF_DATE) as seq
    from @t
    order by POL


    Vinay Valeti| If you think my suggestion is useful, please rate it as helpful. If it has helped you to resolve the problem, please Mark it as Answer

    Monday, October 14, 2013 2:36 AM

All replies

  • declare @t as table(POL int,POL_EFF_DATE date)
    insert into @t values(123,'1-1-2012'),(123,'1-1-2012'),(123,'1-1-2012'),(123,'1-1-2013')
    ,(123,'1-1-2013'),(456,'1-1-2012'),(456,'1-1-2012'),(456,'1-1-2013'),(456,'1-1-2013')
    select * from @t
    select POL,POL_EFF_DATE,
    DENSE_RANK() over (order by POL_EFF_DATE) as seq
    from @t
    order by POL


    ESHANI. Please click "Mark As Answer" if a post solves your problem or "Vote As Helpful" if a post has been useful to you

    Monday, October 14, 2013 1:27 AM
  • Hi,

    If you want to add sequence with the group where pol is equal,we need to add partition by clause to what Eshani has given..Like.

    declare @t as table(POL int,POL_EFF_DATE date)
    insert into @t values(123,'1-1-2012'),(123,'1-1-2012'),(123,'1-1-2012'),(123,'1-1-2013')
    ,(123,'1-1-2013'),(456,'1-1-2012'),(456,'1-1-2012'),(456,'1-1-2013'),(456,'1-1-2013')
    select * from @t
    
    select POL,POL_EFF_DATE,
    DENSE_RANK() over (partition by pol order by POL_EFF_DATE) as seq
    from @t
    order by POL


    Vinay Valeti| If you think my suggestion is useful, please rate it as helpful. If it has helped you to resolve the problem, please Mark it as Answer

    Monday, October 14, 2013 2:36 AM
  • thanks Vinay,

    that worked!

    Monday, October 14, 2013 4:43 PM
  • Please post DDL, so that people do not have to guess what the keys, constraints, Declarative Referential Integrity, data types, etc. in your schema are. Learn how to follow ISO-11179 data element naming conventions and formatting rules (why did you put # in a column header?? Why do you hate SQL so much?). Temporal data should use ISO-8601 formats; your failure here is like not knowing the metric system in Engineering. Code should be in Standard SQL as much as possible and not local dialect. 

    This is minimal polite behavior on SQL forums. 

    Your useless, rude picture is not even a table! It has no key, it has no way to ever have a key!! Here is my guess

    CREATE TABLE Policies
    (policy_nbr CHAR(3) NOT NULL,
     effective_date DATE NOT NULL, 
     PRIMARY KEY (policy_nbr, effective_date),
     occurrence_cnt INTEGER DEFAULT 1 NOT NULL
       CHECK (occurrence_cnt > 0))

    INSERT INTO Policies
    VALUES 
    ('123', '2012-01-01', 3),
    ('123', '2013-01-01', 2),
    ('456', '2012-01-01', 2),
    ('456', '2013-01-01', 2); 
     
    If you had ever read a book on RDBMS, you would know the last column is called the “Degree of Duplication” by Dr. Codd. Do you know what a table is? Normalization? 

    >> I want to add sequence number to each group with same policy_nbr and Effective Date like this:

    SELECT policy_nbr, effective_date, occurrence_cnt,
           ROW_NUMBER() 
            OVER (PARTITION BY policy_nbr ORDER BY effective_date)
            AS policy_seq
      FROM Policies;

    Please get a basic education before you code SQL again.  

    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    Monday, October 14, 2013 7:45 PM