asked
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
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
@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.
select * from ( select a.*,rank() over (partition by a.LBrCode,a.PrdAcctId order by a.DpDate ) rnk_firt, rank() over (partition by a.LBrCode,a.PrdAcctId order by a.DpDate desc ) rnk_last from D009046 a where a.DpDate<='31-mar-2021' ) x where rnk_firt=1 or rnk_last=1 and PrdAcctId='CC 000000000000006800000000' and LBrCode=1
how do I achieve this ?
19 People are following this question.
