Ranking data based on row values

Question

question

Snow asked Mar 23, '18

Ranking data based on row values

Hello there, Appreciate if anyone could help me here. I got this situation that I need to rank a set of data. As shown in the table shown in the attached file below, there is a ID which repeats 3 times due to number of years (2001,2002 & 2003). The values in the Measure column is vary for each year hence I need to rank these values by assigning a Rank No. The highest measure value will be one and the lowest measure value is 3. Can this be done in the SQL query such as using a function such as Rank(). Ranking should be done based on the ID column. ID Year Measure ----------- ----------- ----------- 100 2001 5 100 2002 10 100 2003 8 200 2001 5 200 2002 3 100 2003 8 ![Sample data screenshot:][1] [1]: /storage/temp/4573-4572-sample-data.png

function

4572-sample-data.png (38.9 KiB)

10 |1200 characters needed characters left characters exceeded

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 1 · 2018-03-23T11:42:08Z

Usman Butt answered Mar 23, '18

There are other options possible as well but yes, you can do that using the RANK() function indeed declare @table table ( id int, Year int, Measure int ) insert @table select 100, 2001, 5 union all select 100, 2002, 10 union all select 100, 2003, 8 union all select 200, 2001, 5 union all select 200, 2002, 3 union all select 200, 2003, 8 select *, RANK() over(partition by id order by Measure desc) RankNo from @table order by id, Year

1

10 |1200 characters needed characters left characters exceeded

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Oleg · Mar 23, 2018 at 12:44 PM

@Snow What about the scenario when the measure values for a specific ID are the same for more than one year? Let's say that the value of the measure in the first row (ID = 100, year = 2001) is 10, not 5. What would you like to see for the rank values then? If rank is used then for first 3 rows you will get 1, 1, 3 respectively. This is because the rank is the same if the values are the same. If you need the rank values in this scenario to be evaluated to 1, 1, 2, that is the same rule for rank being the same if the values are the same, but without a "hole", then you might want to use dense\_rank() instead of rank(). Finally, if you would like to still have different rank values even if there are same measure values in more than one row for the same ID then you cannot use rank() or dense\_rank() at all, and should opt for row\_number(), same partition and order by.

2 ·

question