USE Pubs
select [Row #]=count(*), a1.au_lname, a1.au_fname
from authors a1, authors a2
where a1.au_lname + a1.au_fname >= a2.au_lname + a2.au_fname
group by a1.au_lname, a1.au_fname
order by 1
I did a quick execution plan comaprison and I think this may be more efficient?
use pubs select (select count(*) from authors where au_lname + au_fname <= a1.au_lname + a1.au_fname) as Row_Number ,a1.au_lname, a1.au_fname from authors a1 order by a1.au_lname, a1.au_fname
You have to do it on the front end or on the final select statement. What if someone does a select with a WHERE clause on your view? You can only produce row numbers when you know the final question you are asking, and no view can ever know that. If you want a row number for each address no matter how you select them then what you are asking for is just going to mask a missing column in your data model and you should add it (does that make sense??)
Row number on view
Forum Locked
Posted - 08/25/2006 : 11:07:26
