Row number on view - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Old Forums

CLOSED - General SQL Server

Row number on view

Author

Topic

OBINNA_EKE
Posting Yak Master

234 Posts

Posted - 2006-08-25 : 11:07:26

I have a view with Name and Address as colums
I would like to add Rownumber (Extras Colum RowNumber) to it on my view

Note Not on the table itself
Note not Idenetity

Thanks

If it is that easy, everybody will be doing it

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-08-25 : 11:29:04

try this


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

Srinika

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2006-08-25 : 11:39:09

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

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-08-25 : 23:53:05

Where do you want to do show data?
If you use front end application, do numbering there

Madhivanan

Failing to plan is Planning to fail

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-08-26 : 03:45:38

if you are using SQL Server 2005, then check out this link

http://www.sqlservercentral.com/columnists/mcoles/sequentialordering.asp

Chirag

LoztInSpace
Aged Yak Warrior

940 Posts

Posted - 2006-08-27 : 20:21:07

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??)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources