Results 1 to 3 of 3
  1. #1
    Join Date
    Sep 2009
    Posts
    62

    Unanswered: Add Sequence Number within group

    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

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Normally I'd answer this question "Yes" and let it go at that. The question sounds far too much like a homework assignment for me to comfortably just give out an answer, but since I'm familiar with you and you've made contributions to the forum I'll make an exception in this case.
    Code:
    DECLARE @d TABLE (
       [POL#]       INT         NOT NULL
    ,  POL_EFF_DATE DATETIME    NOT NULL
       )
    
    INSERT INTO @d ([POL#], POL_EFF_DATE)
       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 *
    ,  DENSE_RANK() OVER (PARTITION BY [POL#] ORDER BY [POL_EFF_DATE]) AS Seq
       FROM @d
    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  3. #3
    Join Date
    Sep 2009
    Posts
    62
    Thanks Pat,

    I was trying to use Row_Number, but it didn't seem to work the way I wanted. I'd never seen the Dense_Rank function before...

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •