SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
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  

OBINNA_EKE
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

Thanks



If it is that easy, everybody will be doing it

Srinika
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


Srinika
Go to Top of Page

snSQL
Flowing Fount of Yak Knowledge

USA
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

madhivanan
Premature Yak Congratulator

India
22761 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

Madhivanan

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

chiragkhabaria
Flowing Fount of Yak Knowledge

India
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

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

Chirag
Go to Top of Page

LoztInSpace
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.09 seconds. Powered By: Snitz Forums 2000