June 18, 2010 at 4:21 am
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,
June 18, 2010 at 4:29 am
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/61537June 18, 2010 at 5:01 am
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 😛
June 18, 2010 at 9:03 am
Thanks guys.
September 16, 2010 at 9:29 am
That is good for SQL 2005 how about SQL 2000?
September 17, 2010 at 3:51 am
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply