I need to get row number from a view not changing the main select, but it displays the overall rownumber table insted of the selected row rumber.
Here's the values in table1:
field1
---------
aa
ab
ba
bb
bc
ca
cb
cc
I created a view like this:
select field1, row_number() OVER (ORDER BY field1) as rowno
from table1
but a select displays the overall rownumbers:
select *
from myview
where field1 like 'c%'
field1 rowno
-----------------
ca 6
cb 7
cc 8
instead of
field1 rowno
-----------------
ca 1
cb 2
cc 3
any chance to get it right?
I guess you aren't getting the concept of row number right here. If you need row number to start from 1 everytime you are selecting from that view, you should not be selecting the row_number column that is already generated in the select statement of the view, instead, you must write your ROW_NUMBER() part of the condition in every select statement you run against the view. You would need something like this:
SELECT fields1, ROW_NUMBER() OVER (ORDER BY field1) as rowno
FROM <viewname>
WHERE fields1 LIKE 'C%';