If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 
Go Back  dBforums > Database Server Software > Microsoft SQL Server > How to number records, duplicates receiving same number

Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old
Registered User
 
Join Date: Oct 2010
Location: Atlanta, GA
Posts: 154
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.
Reply With Quote
  #2 (permalink)  
Old
Resident Curmudgeon
 
Join Date: Feb 2004
Location: In front of the computer
Posts: 14,791
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.
Reply With Quote
  #3 (permalink)  
Old
Registered User
 
Join Date: Oct 2010
Location: Atlanta, GA
Posts: 154
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.
Reply With Quote
  #4 (permalink)  
Old
Registered User
 
Join Date: Nov 2003
Posts: 2,806
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
Reply With Quote
  #5 (permalink)  
Old
Registered User
 
Join Date: Oct 2010
Location: Atlanta, GA
Posts: 154
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.
Reply With Quote
  #6 (permalink)  
Old
World Class Flame Warrior
 
Join Date: Jun 2003
Location: Ohio
Posts: 12,560
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
Reply With Quote
  #7 (permalink)  
Old
Registered User
 
Join Date: Oct 2010
Location: Atlanta, GA
Posts: 154
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.
Reply With Quote
  #8 (permalink)  
Old
Resident Curmudgeon
 
Join Date: Feb 2004
Location: In front of the computer
Posts: 14,791
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.
Reply With Quote
  #9 (permalink)  
Old
Registered User
 
Join Date: Oct 2010
Location: Atlanta, GA
Posts: 154
Thanks everyone. i'll make sure to add more details in my next question.
Reply With Quote
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On