replacement of dense_rank function in oracle

Database
Enthusiast

replacement of dense_rank function in oracle

Hi all,

We are migrating from oracle to teradata, and got stuck with dense_rank function, please any suggestions would be appreciate. 

Here is our query in oracle: 

select 

dense_rank() OVER (ORDER BY STATE) AS STATE_KEY, 

COALESCE(columnA, 'Other')

from  tableA

left outer join tableB on tableA.sk1 = tableB.sk1

left outer join tableC on tableA.sk2 = tableC.sk2

We've looked some threads and converted into following, but not working properly

SELECT 

--,DENSE_RANK() OVER (ORDER BY STATE) -- not implemented,

COALESCE(columnA, 'Other'), 

  SUM(CASE WHEN STATE = prev_STATE THEN 0 ELSE 1 END)

   OVER (

         ORDER BY STATE, prev_STATE

         ROWS UNBOUNDED PRECEDING) AS STATE_KEY

FROM

 (

  SELECT

     STATE

    ,MIN(STATE) OVER (ORDER BY STATE

           ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING) AS prev_STATE

  FROM tableA

left outer join tableB on tableA.sk1 = tableB.sk1

left outer join tableC on tableA.sk2 = tableC.sk2

 ) 

Tags (2)
2 REPLIES
Enthusiast

Re: replacement of dense_rank function in oracle

Refer to page

http://developer.teradata.com/blog/dnoeth/2011/05/missing-functions-dense-rank

here 3 methods are provided.

Keep outer join outside and not in derived table.

Junior Contributor

Re: replacement of dense_rank function in oracle

Of course this is not correct, you use two columns in the ORDER BY, but the MIN(STATE) is only a single column.

Go to Harpeet's link and change the code to the 2nd version based on 

ROW_NUMBER() OVER (ORDER BY STATE, COALESCE(columnA, 'Other'))

And better keep the outer join, otherwise you'll get a different result :-)

Dieter