Query result with added column number auto incremented dynamically

How do I add extra column to get row number added to my query result and increment dynamically with query resulted rows.

I have tried using How to generate serial number in a query?

select row_number() over (order by e_id) as Sr.No,name,(select role_name from role where r_e_fid = e_id),year from emloyee 
group by e_id,name,year 
order by role_name desc, year asc

But the result is like below

Sr.No   name   role_name     year 
  4     Vasim  DBA           2010
  2     Khasi  Develper      2010
  5     Raj    FullStack     2012
  1     Ghani  FullStack     2012
  3     Shanu  FullStack     2013

Sr.No is getting scuffled.

Answer

If you want the sequence number to be the same as the result set, use the same order by keys. In fact, you can order by the column itself!

select row_number() over (order by e.name_desc, e.year asc) as sr_no, 
       e.name,
       (select r.role_name from role r where r.r_e_fid = e.e_id),
       year
from emloyee e
group by e.e_id, e.name, e.year 
order by sr_no;

Note that I added table aliases and qualified the column references. This is a best practice so queries are understandable, maintainable, and do what you really intend.

Also, if e_id is the primary key in employee, then you can just use group by e.e_id.