Results 1 to 9 of 9
  1. #1
    Join Date
    Oct 2010
    Location
    Atlanta, GA
    Posts
    158

    Question How to number records, duplicates receiving same number

    I have a large table of customers. I would like to add a column that contains an integer, unique to that customer. The trick is that this file contains many duplicate customers, so I want the duplicates to all have the same number between them.

    Tim
    Phil
    Tim
    Tim
    John
    Tammy
    Jill
    Tammy

    would turn into;

    Tim 1
    Phil 2
    Tim 1
    Tim 1
    John 3
    Tammy 4
    Jill 5
    Tammy 4

    Any ideas? the numbers dont have to be sequential or anything, just like customers having the same one.

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    14,910
    If you are running newer versions of SQL Server, there are better answers, but this works back to before Microsoft acquired the product.
    Code:
    CREATE TABLE #clawlan (
       firstName    VARCHAR(9)  NOT NULL
       )
    
    INSERT INTO #clawlan
       SELECT 'Tim'
       UNION ALL SELECT 'Phil'
       UNION ALL SELECT 'Tim'
       UNION ALL SELECT 'Tim'
       UNION ALL SELECT 'John'
       UNION ALL SELECT 'Tammy'
       UNION ALL SELECT 'Jill'
       UNION ALL SELECT 'Tammy'
    
    SELECT * FROM #clawlan
    
    ALTER TABLE #clawlan
       ADD
       id           INT         NULL
    
    SELECT * FROM #clawlan
    
    UPDATE #clawlan
       SET id = (SELECT Count(DISTINCT a.firstName)
          FROM #clawlan AS a
    	  WHERE  a.firstName <= #clawlan.firstName)
    
    SELECT * FROM #clawlan
    
    DROP TABLE #clawlan
    If you can tell us which version you're using, we can probably offer something more succinct and performant.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  3. #3
    Join Date
    Oct 2010
    Location
    Atlanta, GA
    Posts
    158
    Quote Originally Posted by Pat Phelan View Post
    If you are running newer versions of SQL Server, there are better answers, but this works back to before Microsoft acquired the product.
    Code:
    CREATE TABLE #clawlan (
       firstName    VARCHAR(9)  NOT NULL
       )
    
    INSERT INTO #clawlan
       SELECT 'Tim'
       UNION ALL SELECT 'Phil'
       UNION ALL SELECT 'Tim'
       UNION ALL SELECT 'Tim'
       UNION ALL SELECT 'John'
       UNION ALL SELECT 'Tammy'
       UNION ALL SELECT 'Jill'
       UNION ALL SELECT 'Tammy'
    
    SELECT * FROM #clawlan
    
    ALTER TABLE #clawlan
       ADD
       id           INT         NULL
    
    SELECT * FROM #clawlan
    
    UPDATE #clawlan
       SET id = (SELECT Count(DISTINCT a.firstName)
          FROM #clawlan AS a
    	  WHERE  a.firstName <= #clawlan.firstName)
    
    SELECT * FROM #clawlan
    
    DROP TABLE #clawlan
    If you can tell us which version you're using, we can probably offer something more succinct and performant.

    -PatP
    Hi PatP. We are using 2008. Your solution is interesting but not really feasible for me, as this table has 3.8 million records, 2.2 million of those unique.

  4. #4
    Join Date
    Nov 2003
    Posts
    2,815
    Code:
    select name, row_number() over (partition by name order by name)
    from the_table
    I will not read nor answer questions where the SQL code is messy and not formatted properly using [code] tags: http://www.dbforums.com/misc.php?do=bbcode#code

    Tips for good questions:

    http://tkyte.blogspot.de/2005/06/how...questions.html
    http://wiki.postgresql.org/wiki/SlowQueryQuestions
    http://catb.org/esr/faqs/smart-questions.html

  5. #5
    Join Date
    Oct 2010
    Location
    Atlanta, GA
    Posts
    158
    Quote Originally Posted by shammat View Post
    Code:
    select name, row_number() over (partition by name order by name)
    from the_table
    Thanks for this, but it is not exactly what i am looking for. This simply increments duplicates.

  6. #6
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,566
    I think he wants the DENSE_RANK() function:
    Code:
    CREATE TABLE #clawlan (firstName    VARCHAR(9)  NOT NULL)
    
    INSERT INTO #clawlan
       SELECT 'Tim'
       UNION ALL SELECT 'Phil'
       UNION ALL SELECT 'Tim'
       UNION ALL SELECT 'Tim'
       UNION ALL SELECT 'John'
       UNION ALL SELECT 'Tammy'
       UNION ALL SELECT 'Jill'
       UNION ALL SELECT 'Tammy'
       
    select firstname,
    	row_number() over (order by firstname) as RowNumberValue,
    	rank() over (order by firstname) as RankValue,
    	dense_rank() over (order by firstname) as DenseRankValue	
    from #clawlan
       
    drop table #clawlan
    firstname RowNumberValue RankValue DenseRankValue
    --------- -------------------- -------------------- --------------------
    Jill 1 1 1
    John 2 2 2
    Phil 3 3 3
    Tammy 4 4 4
    Tammy 5 4 4
    Tim 6 6 5
    Tim 7 6 5
    Tim 8 6 5
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  7. #7
    Join Date
    Oct 2010
    Location
    Atlanta, GA
    Posts
    158
    Quote Originally Posted by blindman View Post
    I think he wants the DENSE_RANK() function:
    Code:
    CREATE TABLE #clawlan (firstName    VARCHAR(9)  NOT NULL)
    
    INSERT INTO #clawlan
       SELECT 'Tim'
       UNION ALL SELECT 'Phil'
       UNION ALL SELECT 'Tim'
       UNION ALL SELECT 'Tim'
       UNION ALL SELECT 'John'
       UNION ALL SELECT 'Tammy'
       UNION ALL SELECT 'Jill'
       UNION ALL SELECT 'Tammy'
       
    select firstname,
    	row_number() over (order by firstname) as RowNumberValue,
    	rank() over (order by firstname) as RankValue,
    	dense_rank() over (order by firstname) as DenseRankValue	
    from #clawlan
       
    drop table #clawlan
    firstname RowNumberValue RankValue DenseRankValue
    --------- -------------------- -------------------- --------------------
    Jill 1 1 1
    John 2 2 2
    Phil 3 3 3
    Tammy 4 4 4
    Tammy 5 4 4
    Tim 6 6 5
    Tim 7 6 5
    Tim 8 6 5
    Ah dense_rank! Thank you.

  8. #8
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    14,910
    Quote Originally Posted by clawlan View Post
    Hi PatP. We are using 2008. Your solution is interesting but not really feasible for me, as this table has 3.8 million records, 2.2 million of those unique.
    Thanks for an excellent example of how providing even a few more details about your problem leads to radically better answers!

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  9. #9
    Join Date
    Oct 2010
    Location
    Atlanta, GA
    Posts
    158
    Thanks everyone. i'll make sure to add more details in my next question.

Posting Permissions

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