Incremental value based on duplication of fields
-
Hi,
In a temporary table (Table_A) I have several instances of duplicate records. When loading these into Table_B there is a Primary Key based on 4 fields. 3 of these values will be populated from Table_A and the 4th should be a sequential count based on the number of instances the 3 fields match.
Example
Table_A (contains approx 500,000 records)
K1, K2, K3,
17, 18, AA,
17, 18, AA,
17, 19, AA,
18, 19, BB,
18, 19, BB,
18, 19, BB,
19, 19, BB,
19, 20, AA,
Table_B (with additional sequence identifier)
K1, K2, K3, S1
17, 18, AA, 1
17, 18, AA, 2
17, 19, AA, 1
18, 19, BB, 1
18, 19, BB, 2
18, 19, BB, 3
19, 19, BB, 1
19, 20, AA, 1
Any ideas please?
Thanks in advance,
-
Look up ROW_NUMBER() OVER... in BOL
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537
-
DECLARE @table AS TABLE(k1 INT,
k2 INT,
k3 VARCHAR(2))
INSERT INTO @table (k1,k2,k3)
SELECT 17, 18, 'AA'
UNION ALL SELECT 17, 18, 'AA'
UNION ALL SELECT 17, 19, 'AA'
UNION ALL SELECT 18, 19, 'BB'
UNION ALL SELECT 18, 19, 'BB'
UNION ALL SELECT 18, 19, 'BB'
UNION ALL SELECT 19, 19, 'BB'
UNION ALL SELECT 19, 20, 'AA'
SELECT k1,
k2,
k3,
Row_number() OVER (PARTITION BY k1, k2 ORDER BY k3 DESC) AS s1
FROM @table
-edit-
Damn. Posted that way too slow 😛
Forever trying to learn
My blog - http://www.cadavre.co.uk/
For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/ -
Thanks guys.
-
That is good for SQL 2005 how about SQL 2000?
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply