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

Question

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

Fergus Bown · Accepted Answer · 2012-12-06 10:26:50Z

1

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?

edited Dec 6, 2012 at 10:26

answered Dec 5, 2012 at 13:43

Fergus Bown

1,6969 silver badges16 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Brian White Over a year ago

This is a fine answer. If you want it to be a real column just add a new null column and fill it in like this. This is fine if your instrument ids are 1000, 2000, 3000. If they're 1, 2, 3, 1, 2, then you're messed up, because you'll get 1 turned into 11 and turned into 12. So a tiny addition, add a period and make your column a decimal. So that way you get 1 turned into 1.1 and 1.2 instead of 11 and 12.

user1878878 Over a year ago

Thanks for your Answer Fergus; what I have is 2 tables which need to be joined together on the incremented instrumentID and, for every start date, there is a corresponding end date (and only 1). So there is a "start date" table and an "end date" table and each should be incremented in date order so that I will end up with a start date next to an end date in my final table following the JOIN. The RANK function is useful, but only returns 1 for the top rank, then two etc whereas I need it to increment within the partition. Also, thanks for introducing me to OVER.

user1878878 Over a year ago

Thanks Fergus - you're answer is almost identical to the one I came here to write today but I write the RANK OVER during the table CREATE. This is because I want to be able to verify random samples from both tables.

Collectives™ on Stack Overflow

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

1 Answer 1

3 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Related