Post reply

Assign a sequential number to each row in a view

  • 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.

     

     

  • March 9, 2006 at 6:57 am

    #625654

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







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

  • March 9, 2006 at 7:02 am

    #625659

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

  • March 9, 2006 at 7:05 am

    #625661

    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.

     

  • March 9, 2006 at 7:18 am

    #625668

    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

  • March 9, 2006 at 7:22 am

    #625675

    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.

     

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

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