| |
|
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.
|
 |
02-19-13, 12:26
|
|
Registered User
|
|
Join Date: Oct 2010
Location: Atlanta, GA
Posts: 87
|
|
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.
|
|
02-19-13, 12:41
|
|
Resident Curmudgeon
|
|
Join Date: Feb 2004
Location: In front of the computer
Posts: 13,651
|
|
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.
|
|
02-19-13, 12:59
|
|
Registered User
|
|
Join Date: Oct 2010
Location: Atlanta, GA
Posts: 87
|
|
|
|
Quote:
Originally Posted by Pat Phelan
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.
|
|
02-19-13, 13:06
|
|
Registered User
|
|
Join Date: Nov 2003
Posts: 2,684
|
|
Code:
select name, row_number() over (partition by name order by name)
from the_table
|
|
02-19-13, 14:03
|
|
Registered User
|
|
Join Date: Oct 2010
Location: Atlanta, GA
Posts: 87
|
|
Quote:
Originally Posted by shammat
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.
|
|
02-19-13, 14:05
|
|
World Class Flame Warrior
|
|
Join Date: Jun 2003
Location: Ohio
Posts: 12,356
|
|
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
|
|
02-19-13, 14:35
|
|
Registered User
|
|
Join Date: Oct 2010
Location: Atlanta, GA
Posts: 87
|
|
Quote:
Originally Posted by blindman
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.
|
|
02-19-13, 14:38
|
|
Resident Curmudgeon
|
|
Join Date: Feb 2004
Location: In front of the computer
Posts: 13,651
|
|
Quote:
Originally Posted by clawlan
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.
|
|
02-19-13, 15:24
|
|
Registered User
|
|
Join Date: Oct 2010
Location: Atlanta, GA
Posts: 87
|
|
Thanks everyone. i'll make sure to add more details in my next question.
|
|
| Thread Tools |
Search this Thread |
|
|
|
| Display Modes |
 Linear Mode
|
Posting Rules
|
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
HTML code is Off
|
|
|
|
|
dBforums
>
How to number records, duplicates receiving same number
