Search code examples
couchbasesql++

couchbase: how to add sr number to the results in asc order

I want to add one column in the first which shows the sr.no (sequence number) in ascending order.

example: (here the sequence number will not be order, rather follow the order of plan_year)

SELECT  ROW_NUMBER() OVER(),
       plan_name,
       plan_year
FROM `prod-fliptrx-app`
WHERE type = 'something'
ORDER BY plan_year DESC

so how to ensure in the above command i see the seq number in asc order irrespective of the ORDER BY attribute in the end

Solution

  • ROW_NUMBER() OVER() always increase number. As you don't have any ORDER BY specified in ROW_NUMBER(), it assigns seq no number but your ORDER BY clause of query changes order. As projection is done before query ORDER BY.

    You can generate ROW_NUMBER() on plan_year DESC and sort by row_number like below:

    SELECT  ROW_NUMBER() OVER( ORDER BY plan_year DESC) AS rnum,
       plan_name,
       plan_year
FROM `prod-fliptrx-app`
WHERE type = 'something'
ORDER BY rnum;