SQL Server – Adding ID Column Based on Two Other Columns

cursorsranksql serversql-server-2008-r2

I have a query which returns output as follows:

 [Location] [Reference] [Year] [Int1] [Int2]
    England           1   2015     13    201
    England           1   2015     12     57
    England           1   2015      4     14
    England           2   2015     18     29
    England           2   2015     18     29
    England           1   2016     32     67
    England           1   2016     43     11
    England           2   2016     10     56

Where the int1 and int2 columns represent some calculated data regarding the locations.
I am trying to represent the data in a report and realized I could use an ID column of the form:

 [ID][Location] [Reference] [Year] [Int1] [Int2]
    1   England           1   2015     13    201
    2   England           1   2015     12     57
    3   England           1   2015      4     14
    1   England           2   2015     18     29
    2   England           2   2015     33     12
    1   England           1   2016     32     67
    2   England           1   2016     43     11
    1   England           2   2016     10     56

Which is incremented for every row which has the same Reference and Year values.

I have so far tried to achieve this using the Ranking functions as in the example below but with little luck. 

SELECT Location, Reference, Year, Int1, Int2,
DENSE_RANK() OVER (PARTITION BY Year ORDER BY Reference ASC) AS rnk
FROM Value

Returns 

 [Location] [Reference] [Year] [Int1] [Int2][rnk]
    England           1   2015     13    201    1
    England           1   2015     12     57    1
    England           1   2015      4     14    1
    England           2   2015     18     29    2
    England           2   2015     33     12    2
    England           1   2016     32     67    1
    England           1   2016     43     11    1
    England           2   2016     10     56    2

So my question would be is there a way to use the rank functions, Rank, DENSE_RANK etc in order to achieve this? Failing that what might my best option be?

It has been suggested that a CURSOR may help me order the data in this way but after reading up on its use I would like to determine whether a set based approach is available.

I tried to create an SQLFiddle for this question but it seems I can't create a SQL Server version at the moment. I've created the this one using MySQL 5.6 so you at least have access to the schema. Apologies.

Best Answer

You just need

SELECT Location, 
       Reference, 
       Year,  
       Int1, 
       Int2,
       ROW_NUMBER() OVER (PARTITION BY Year, Reference 
                              ORDER BY Int2 DESC) AS [Id]
FROM Value

To give your desired results (online demo).

In the event of tied Year,Reference,Int2 this will arbitrarily assign a sequential numbering between the tied rows.

Related Question