Row_Number() alternative in Couchbase 6.0

Couchbase Server
1

I have an oracle query which I want to translate into corresponding Couchbase’s n1ql query.

select EMPNO, to_date,from_date, 
row_number() over (order by from_date asc) as JoiningRank,
row_number() over (order by from_date desc) as ExperinceRank
from [dbo].[EMPHISTORY]

As Row_Number() is being used here, I could not find an alternate way to write this query in n1ql.

I read somewhere that Row_Number() function (window functions) is available in 6.5 version.

  1. Since I am using 6.0 right now, is there any way to implement this query in n1ql?
  2. Does Couchbase 6.5 support this Row_Number() function? If yes, only EE or CE as well ?

Please help to re-write this query in n1ql.

2

window functions are supported in CB 6.50 EE.

There is no easy way to do in 6.0. As you don’t have PARTITION BY If the data is small you can try this

SELECT d2.*, UNNEST_POS(d2)+1 AS JoiningRank, ARRAY_LENGTH(d.av)-UNNEST_POS(d2) AS ExperinceRank
FROM (SELECT (SELECT EMPNO, to_date, from_date,
          FROM default
          WHERE ....
          ORDER BY from_date) AS av
     ) AS d
UNNEST d.av AS d2
1 Like