Search code examples
sqlfunctionranking

Is it possible to rank the data in following order using SQL ranking functions

I have data in a table and it needs to be ordered by Brand column and ranked as ClassCode changes. I have tried using, ROW_NUMBER, RANK, DENSE_RANK functions using PARTITION BY but not getting the results I need.

As ClassCode changes the rank should be increased and I can't use the PARTITION BY for ClassCode because it changes the order of Brands.

Example table: 

Code Brand    ClassCode
101  Sony     201
103  LG       202
104  Nokia    203
107  Sony     201
108  Samsung  202

Table data needs to be ordered by Brand and ranked by ClassCode changes like below:

Required Results:

Code Brand    ClassCode  rnk
103  LG       202        1
104  Nokia    203        2
108  Samsung  202        3
101  Sony     201        4
107  Sony     201        4

Note: Once I have the order and ranks i need to insert the results into another table. I understand the order by should be done in only SELECT statements but unfortunately the application selects data from a table that has been populated with all the order and ranking logic.

Solution

  • This is a gaps-and-islands problem, where you are trying to find the number of times the classcode changes based on the code ordering.

    This should return the results you want:

    select t.*,
       sum(case when prev_cc = classcode then 0 else 1 end) over (order by code) as ranking
from (select t.*, lag(classcode) over (order by code) as prev_cc
      from t
     ) t