CODEWITHSUNDEEP
sqlsql-server-2008t-sql
user1878878
user1878878

Reputation: 23

How to increment a value in SQL based on a unique key

Apologies in advance if some of the trigger solutions already cover this but I can't get them to work for my scenario.

I have a table of over 50,000 rows, all of which have an ID, with roughly 5000 distinct ID values. There could be 100 rows with an instrumentID = 1 and 50 with an instrumentID = 2 within the table etc but they will have slightly different column entries. So I could write a 

SELECT * from tbl WHERE instrumentID = 1

and have it return 100 rows (I know this is easy stuff but just to be clear)

What I need to do is form an incrementing value for each time a instrument ID is found, so I've tried stuff like this:

    IntIndex INT IDENTITY(1,1), 
    dDateStart DATE,
    IntInstrumentID INT,
    IntIndex1 AS IntInstrumentID + IntIndex,

at the table create step.

However, I need the IntIndex1 to increment when an instrumentID is found, irrespective of where the record is found in the table so that it effectively would provide a count of the records just by looking at the last IntIndex1 value alone. Rather than what the above does which is increment on all of the rows of the table irrespective of the instrumentID so you would get 5001,4002,4003 etc.

An example would be: for intInstruments 5000 and 4000

intInstrumentID  | IntIndex1
--------- ------------------
    5000         |   5001
    5000         |   5002
    4000         |   4001
    5000         |   5003
    4000         |   4002

The reason I need to do this is because I need to join two tables based on these values (a start and end date for each instrumentID). I have tried GROUP BY etc but this can't work in both tables and the JOIN then doesn't work.

Many thanks

Upvotes: 2

Views: 1125

Answers (1)

Fergus Bown
Fergus Bown

Reputation: 1696

I'm not entirely sure I understand your problem, but if you just need IntIndex1 to join to, could you just join to the following query, rather than trying to actually keep the calculated value in the database:

SELECT  *, 
        intInstrumentID + RANK() OVER(PARTITION BY intInstrumentID ORDER BY dDateStart ASC) AS IntIndex1
FROM    tbl

Edit: If I understand your comment correctly (which is not certain!), then presumably, you know that your end date and start date tables have the exact same number of rows, which leads to a one to one mapping between them based on thir respective end dates within instrument id?

If that's the case then maybe this join is what you are looking for:

SELECT SD.intInstrumentID, SD.dDateStart, ED.dEndDate
FROM
(
    SELECT  intInstrumentID, 
            dStartDate, 
            RANK() OVER(PARTITION BY intInstrumentID ORDER BY dDateStart ASC) AS IntIndex1
    FROM    tblStartDate
) SD
JOIN
(
    SELECT  intInstrumentID, 
            dEndDate, 
            RANK() OVER(PARTITION BY intInstrumentID ORDER BY dEndDate ASC) AS IntIndex1
    FROM    tblStartDate
) ED
    ON  SD.intInstrumentID = ED.intInstrumentID
    AND SD.IntIndex1 = ED.IntIndex1

If not, please will you post some example data for both tables and the expected results?

Upvotes: 1

Related Questions