Get DENSE RANK in a grouped data

[DSguru2B]

It doesnt matter. The rule is
For subsequent rows, if the first three columns are same and the fifth column is different, only then increment, else 1.
DId you try coding as I suggested.

[Dsnew]

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 ???

Did not make much sense to me, have not done anything like that before.
Can you please provide more details..

[DSguru2B]

Three stage variables, PrevThree, PrevSales and cond. Their derivations will be

Code: Select all

cond      |    if @INROWNUM =1 then 1 else if (in.STATE:in.CITY:in.ZIP=  
               PrevThree) AND (in.SALES <> PrevSales) then cond +1 else 1
PrevThree |    in.STATE:in.CITY:in.ZIP
PrevSales |    in.SALES

The stage variables should be in the same order. The stage variable cond will have your Rank.

[narasimha]

You can do it in Oracle
Assuming your table name is Car.

Code: Select all

SELECT   state, city, zip, sales_number,
         dense_rank() OVER (PARTITION BY state, city, zip ORDER BY sales_number DESC) rank
    FROM Car
ORDER BY state, city, zip, sales_number DESC

Simple!

[ganesh123]

DSGuru2B can you show it using RowProcCompareWithPreviousValue

I am doing the following but not getting :

Code: Select all

1)current=read.ST:read.CITY:read.ZIP
2)sv1=RowProcCompareWithPreviousValue(current)
3)sv2=if sv1 then RowProcCompareWithPreviousValue(read.SALES) else Rank=1
4)Rank=if sv2 then sv2 else Rank+1 

[ray.wurlod]

My guess is that you need the Transformer stage downstream of an Aggregator stage.

And, of course, data pre-sorted on the aggregation (grouping) columns.

[DSguru2B]

You dont need to use the RowProcCompareWithPreviousValue for more than one time, I think. Just use the code I gave you. It will give you the Rank as you want. Its tested code.

[Dsnew]

You can do it in Oracle
Assuming your table name is Car.

Code: Select all

SELECT   state, city, zip, sales_number,
         dense_rank() OVER (PARTITION BY state, city, zip ORDER BY sales_number DESC) rank
    FROM Car
ORDER BY state, city, zip, sales_number DESC

Simple!

The query works great! Thanks narasimha.
I have one way to do it now.

DSGuru2b, I still having problem getting your code to work. I am getting all Ranks as 1

[DSguru2B]

Did you do exactly what I asked you to do? Sorting, order of stage variables etc. As I said, thats tested code. If it works for me, it will work for you. Go through my post again and do exactly as I said. Can't help you more then that.

[Dsnew]

DSguru2B,

I rechecked it, it works fine for everything except when the sales_number is the same within a group of state, city and metro.
In such conditions we get a 1 instead of the getting the same rank again, because of the condition
(in.SALES <> PrevSales) then cond +1 else 1.
Therefore if in.SALES = PrevSales you will get a 1

Appreciate your help

[DSguru2B]

Ok, I thought it was to be reset to 1. My bad. Use the following

Code: Select all

cond      |    if @INROWNUM =1 then 1 else if (in.STATE:in.CITY:in.ZIP=  
               PrevThree) AND (in.SALES <> PrevSales) then cond +1 else if  
               (in.STATE:in.CITY:in.ZIP= PrevThree) AND (in.SALES = 
               PrevSales) then cond else 1 
PrevThree |    in.STATE:in.CITY:in.ZIP 
PrevSales |    in.SALES

[Dsnew]

Kool! Works like a charm!
I have both ways now.
You guys rock