Simple - Return line number in view

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

Simple - Return line number in view

Author

Topic

staticbob
Yak Posting Veteran

99 Posts

Posted - 2006-02-06 : 07:12:40

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

12543 Posts

Posted - 2006-02-06 : 07:22:13

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.

staticbob
Yak Posting Veteran

99 Posts

Posted - 2006-02-06 : 07:47:31

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

nr
SQLTeam MVY

12543 Posts

Posted - 2006-02-06 : 07:51:13

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.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2006-02-06 : 07:59:37

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

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-02-06 : 08:10:43

I think all reporting tools support RecordNumber feature that you can make use of

Madhivanan

Failing to plan is Planning to fail

Kristen
Test

22859 Posts

Posted - 2006-02-06 : 08:16:05

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

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2006-02-06 : 08:23:35

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.

Frank Kalis
Constraint Violating Yak Guru

413 Posts

Posted - 2006-02-06 : 09:07:17

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources