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
 Simple - Return line number in view
 Forum Locked
 Printer Friendly
Author Previous Topic Topic Next Topic  

staticbob
Yak Posting Veteran

United Kingdom
99 Posts

Posted - 02/06/2006 :  07:12:40  Show Profile
Guys,

AS well as all the fields I am showing from the required tables, I would also like to return a field "LineItemID" that returns the line number of each row in the view ? How can I do this.

Thanks
Bob

"I dislilke 7am. If &am were a person, I would kick 7am in the biscuits." - Paul Ryan, dailyramblings.com

nr
SQLTeam MVY

United Kingdom
12543 Posts

Posted - 02/06/2006 :  07:22:13  Show Profile  Visit nr's Homepage
You will need to clculate it.
Couold insert into a temp table with an identity and select from that. Depends what you relly need.
Why not do it in the client.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Edited by - nr on 02/06/2006 07:24:12
Go to Top of Page

staticbob
Yak Posting Veteran

United Kingdom
99 Posts

Posted - 02/06/2006 :  07:47:31  Show Profile
Thanks for the response nr,

I can't do it in the Client because the reporting tool that I'm using is crap. I can just show fields from a datasource, not add calculated ones.

Bob

"I dislilke 7am. If &am were a person, I would kick 7am in the biscuits." - Paul Ryan, dailyramblings.com
Go to Top of Page

nr
SQLTeam MVY

United Kingdom
12543 Posts

Posted - 02/06/2006 :  07:51:13  Show Profile  Visit nr's Homepage
In that case it's probably best to show the code you have.
You should be use a stored procedure for the interface so that you can deliver whatever is needed.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.
Go to Top of Page

Lamprey
Flowing Fount of Yak Knowledge

4614 Posts

Posted - 02/06/2006 :  07:59:37  Show Profile
Try looking up IDENTITY in books online. They have a sample that might help. A quick example using the Pubs db:

SELECT
   IDENTITY(int, 1, 1) AS num, 
   emp_id AS emp_num, 
   fname AS first, 
   minit AS middle, 
   lname AS last 
INTO employees 
FROM employee
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22755 Posts

Posted - 02/06/2006 :  08:10:43  Show Profile  Send madhivanan a Yahoo! Message
I think all reporting tools support RecordNumber feature that you can make use of

Madhivanan

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

Kristen
Test

United Kingdom
22403 Posts

Posted - 02/06/2006 :  08:16:05  Show Profile
As Lamprey says, but with a slight modification:

SELECT
   IDENTITY(int, 1, 1) AS num, 
   emp_id AS emp_num, 
   fname AS first, 
   minit AS middle, 
   lname AS last 
INTO #MyTempTable
FROM employee
ORDER BY MySortColumn1, MySortColumn2 ...

SELECT *
FROM #MyTempTable
ORDER BY [num]


Ideally pre-create the #MyTempTable and name the columns instead of using SELECT *

Kristen

Edited by - Kristen on 02/06/2006 08:41:19
Go to Top of Page

jsmith8858
Dr. Cross Join

USA
7423 Posts

Posted - 02/06/2006 :  08:23:35  Show Profile  Visit jsmith8858's Homepage
quote:
Originally posted by staticbob

Thanks for the response nr,

I can't do it in the Client because the reporting tool that I'm using is crap. I can just show fields from a datasource, not add calculated ones.

Bob

"I dislilke 7am. If &am were a person, I would kick 7am in the biscuits." - Paul Ryan, dailyramblings.com



What reporting tool are you using?

And, remember, by defintion a view has no ordering -- it is a set of rows to be returned that the caller can order any way that it wants. For those reasons, a View should never return a row # column.
Go to Top of Page

Frank Kalis
Constraint Violating Yak Guru

Switzerland
413 Posts

Posted - 02/06/2006 :  09:07:17  Show Profile  Visit Frank Kalis's Homepage
If you're on SQL Server 2005 you can use ROW_NUMBER().

--
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Heute schon gebloggt? http://www.insidesql.de/blogs
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