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