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

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
Save Password
Forgot your Password?

 All Forums
 Old Forums
 CLOSED - General SQL Server
 Row number on view
 Forum Locked
 Printer Friendly
Author Previous Topic Topic Next Topic  

Posting Yak Master

234 Posts

Posted - 08/25/2006 :  11:07:26  Show Profile
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


If it is that easy, everybody will be doing it

Flowing Fount of Yak Knowledge

Sri Lanka
1378 Posts

Posted - 08/25/2006 :  11:29:04  Show Profile
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

Go to Top of Page

Flowing Fount of Yak Knowledge

1837 Posts

Posted - 08/25/2006 :  11:39:09  Show Profile
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
Go to Top of Page

Premature Yak Congratulator

22864 Posts

Posted - 08/25/2006 :  23:53:05  Show Profile  Send madhivanan a Yahoo! Message
Where do you want to do show data?
If you use front end application, do numbering there


Failing to plan is Planning to fail
Go to Top of Page

Flowing Fount of Yak Knowledge

1907 Posts

Posted - 08/26/2006 :  03:45:38  Show Profile  Visit chiragkhabaria's Homepage  Send chiragkhabaria a Yahoo! Message
if you are using SQL Server 2005, then check out this link

Go to Top of Page

Aged Yak Warrior

940 Posts

Posted - 08/27/2006 :  20:21:07  Show Profile
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??)
Go to Top of Page
  Previous Topic Topic Next Topic  
 Forum Locked
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.03 seconds. Powered By: Snitz Forums 2000