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?

  • Almost impossible. My advise is upgrade to SQL Server 2005, then start looking at Oracle which has top() over(), lag, lead, and a number of other really cool analytic functions.

    Kidding 🙂 Well, sort of.

    Random Technical Stuff[/url]

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply