Assign a sequential number to each row in a view

Question

Post reply

Assign a sequential number to each row in a view

rschaeferhig

SSCrazy Eights

Points: 8776
More actions
March 9, 2006 at 6:37 am

#152813

I have a view that results in entries:
Place     Points Ties
1           136     2
3           134     3
6           132     2 etc...
I want to add a column to the view that assigns a incremental value to the rows, almost like an Identity(1,1) in a table:
Place     Points Ties Position
1           136     2      1
3           134     3      2
6           132     2      3 etc...
Any ideas? I'm clueless on this one without writing an SP that inserts the view into a table variable with an identity column and then spits the contents of the table variable out.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

sushila SSC-Dedicated Points: 35293 More actions · Answer 1

Why not take the easy way out and do it client-side ?!

**ASCII stupid question, get a stupid ANSI !!!**

allen davidson SSCertifiable Points: 7042 More actions · Answer 2

On sql2k I think you are limited to using an IDENTITY - on 2005 you have a ROW_NUMBER() function.

rschaeferhig SSCrazy Eights Points: 8776 More actions · Answer 3

This view gets joined to a table of people/points by the number of points. It tells me for each person not only how many points they have but whether or not they're tied with anyone and what position they're in (the new column). I'd like to build a view that has all the fields in one row so I only have to do one read from the web server. Otherwise I will need to do two reads. It's not really the easy way out if you consider the amount of web server coding if there's a way to do it in the view.

rsharma SSCarpal Tunnel Points: 4865 More actions · Answer 4

If you can use the column Place from the view as a matching criteria, then you can generate the running number (this may or may not be applicable in your scenario - I am just going by the data that you have...example:

--Assume that the table variable shown below is the view in your case.

declare @table table

(

Place int,

Points int,

Ties int

)

--This is the data that you will be getting out of the view

insert into @table values (1, 136, 2)

insert into @table values (3, 134, 3)

insert into @table values (6, 132, 2)

--The query

select a.Place, a.Points, a.Ties,

(select count(1) rownum from @table where Place <= a.Place) rownum

from @table a

--The output

Place Points Ties rownum

----------- ----------- ----------- -----------

1 136 2 1

3 134 3 2

6 132 2 3

rschaeferhig SSCrazy Eights Points: 8776 More actions · Answer 5

I finally said the heck with it and wrote an SP:

CREATE PROCEDURE [DBO].[mystats]

(

@poolid int,

@empid varchar(50),

@pos int output,

@pts int output,

@ties int output,

@ahead int output

)

AS

BEGIN

SELECT @pts=TotalPoints FROM playerStandings WHERE <A href="mailtolayerID=@empid">PlayerID=@empid and <A href="mailtooolID=@poolid">PoolID=@poolid

SELECT @pos=count(*) FROM vw_Ties WHERE <A href="mailtooolID=@poolid">PoolID=@poolid AND Points>=@pts

SELECT @ties=count(PlayerID) FROM playerStandings WHERE TotalPoints=@pts AND PlayerID <> @empid and <A href="mailtooolID=@poolid">PoolID=@poolid

SELECT @ahead=count(PlayerID) FROM playerStandings WHERE TotalPoints>@pts and <A href="mailtooolID=@poolid">PoolID=@poolid

END

GO

It works.