Get DENSE RANK in a grouped data

[Dsnew]

I need to get the rank (basically dense rank) of the SALES_NUMBER based on the CAR_BRAND in a particular group based on STATE, CITY, ZIP

My source and target tables are oracle. can anybody point me in the right direction.

Source
--------

Code: Select all

STATE      CITY              ZIP CAR_BRAND  SALES_NUMBER
---------- ---------- ---------- ---------- ------------
CA         SACRAMENTO      95834 NISSAN              500
NY         NY              10038 LEXUS               100
NY         NY              10038 BMW                 200
NY         NY              10038 VOLVO               250
PA         MILLMONT        17845 BMW                  50
PA         MILLMONT        17845 VOLVO                50
PA         MILLMONT        17845 HONDA               150
TX         SAN MARCOS      78666 VOLVO                50
TX         SAN MARCOS      78666 BMW                 100

Output required
------------------

Code: Select all

STATE      CITY              ZIP CAR_BRAND  SALES_NUMBER  RANK
---------- ---------- ---------- ---------- ------------ ------
CA         SACRAMENTO      95834 NISSAN              500    1
NY         NY              10038 LEXUS               100    1
NY         NY              10038 BMW                 200    2
NY         NY              10038 VOLVO               250    3
PA         MILLMONT        17845 BMW                  50    1
PA         MILLMONT        17845 VOLVO                50    1
PA         MILLMONT        17845 HONDA               150    2
TX         SAN MARCOS      78666 VOLVO                50    1
TX         SAN MARCOS      78666 BMW                 100    2

Appreciate your response

Thanks
Clara

[ganesh123]

You need to give some more information about how you give RANKS ? Like based on what conditions ? The output you shown is confusing. We cannot guess the requirement.....

[gateleys]

1. Sort your input based on STATE, CITY, ZIP.
2. Use a stage variable to remember the concatenated string State:City:Zip of the previous row and compare it with the current row's concatenated value. You will use the RowProcCompareWithPreviousValue function available in the SDK to achieve this.
3. Use a derivation such that if current string is new, then RANK = 1, else RANK = RANK + 1.

gateleys

[DSguru2B]

It looks like a goup change detection and giving a running number. Its been covered a lot. Search the forum for various ways. One has been described by gateleys.

[Dsnew]

There is a little more to it -

If you check the 5th and 6th row, as the SALES_NUMBERS are same they should get the same rank "1" and the successive rank after the 2 consecutive rank 1's should get 2 not 3.
(Rank Vs Dense Rank)

[ray.wurlod]

Does "dense rank" imply two levels of aggregation?

[ganesh123]

Use a stage variable to remember the concatenated string State:City:Zip of the previous row and compare it with the current row's concatenated value. You will use the RowProcCompareWithPreviousValue function available in the SDK to achieve this.

In that case also concate SALES_NUMBER and compare with the previous row.

[Dsnew]

That might not work in this case, ganesh123

Ray, Dense Rank(Oracle) would mean getting the 1,1,2 as opposed to Rank 1,1,3.
Did I get your question right?

[ray.wurlod]

No, but my assumption was off the mark. In your case "dense rank" appears to mean rank without gaps left by ties.

[DSguru2B]

That might not work in this case, ganesh123

Well, if you add the fourth column in the grouping, I dont see why It wont work. You will get 1,1,2.

[gateleys]

That might not work in this case, ganesh123

Resource-consuming............yeah, it may be.
But, not work ..................... did you try it? And if yes, can you spit the results out?

gateleys

[Dsnew]

That might not work in this case, ganesh123

Well, if you add the fourth column in the grouping, I dont see why It wont work. You will get 1,1,2.

You will get 1,1,2 for rows 5, 6, 7 but all the others will get a rank 1

[gateleys]

OK, what if you change the step 3 logic to -

3. Use a derivation such that if (current string is new and PreviousSalesNum = SalesNum), then RANK = 1, else if RANK = RANK + 1.

NOTE: You will need another stage variable to store previuos SalesNum

gateleys

[DSguru2B]

Well you will have to do a column by column check then. Compare column 1, if they are same, check scond column, if same check third, if same, check the 5th column.
Now if the 5th column is same, refresh the rank to 1, if its different, increment the rank.
If any of the first three columns are different, start from 1 again.
Makes sense ???

[Dsnew]

After sorting the rank should look like below.
I had not sorted in the desc order in my previous post. Sorry about that.

Code: Select all

STATE      CITY              ZIP CAR_BRAND  SALES_NUMBER  RANK
---------- ---------- ---------- ---------- ------------ ------- 
CA         SACRAMENTO      95834 NISSAN              500   1
NY         NY              10038 VOLVO               250   1
NY         NY              10038 BMW                 200   2
NY         NY              10038 LEXUS               100   3
PA         MILLMONT        17845 HONDA               150   1 
PA         MILLMONT        17845 BMW                  50   2
PA         MILLMONT        17845 VOLVO                50   2
TX         SAN MARCOS      78666 BMW                 100   1 
TX         SAN MARCOS      78666 VOLVO                50   2