Results 1 to 6 of 6
  1. #1
    Join Date
    Jan 2004
    Posts
    164

    Unanswered: counting records in a view

    I was wondering if i would be able to add a column in a view that assigns a value to each record and incriments the number each time by 1. Like the way an identity field works in a table.

    Is this possible using a view?

  2. #2
    Join Date
    Dec 2002
    Posts
    1,245
    Quote Originally Posted by estefex
    I was wondering if i would be able to add a column in a view that assigns a value to each record and incriments the number each time by 1. Like the way an identity field works in a table.

    Is this possible using a view?
    One of the more frequently asked questions. Not doable in 7.0 or 2000. You can try something similar by creating a temp table (with an identity column); or you can do it with a table function.

    Regards,

    hmscott
    Have you hugged your backup today?

  3. #3
    Join Date
    Jan 2004
    Posts
    164
    Quote Originally Posted by hmscott
    One of the more frequently asked questions. Not doable in 7.0 or 2000. You can try something similar by creating a temp table (with an identity column); or you can do it with a table function.

    Regards,

    hmscott

    Thanks for your reply. I was hoping i would be able to work around it without having to create a table, but it looks like that might just be the way do go afterall.

    Thanks again,

    Steve

  4. #4
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    How many rows are we talking about?

    How about the DDL for the table
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  5. #5
    Join Date
    Jan 2004
    Posts
    164
    Quote Originally Posted by hmscott
    One of the more frequently asked questions. Not doable in 7.0 or 2000. You can try something similar by creating a temp table (with an identity column); or you can do it with a table function.

    Regards,

    hmscott

    Thanks for your reply. I was hoping i would be able to work around it without having to create a table, but it looks like that might just be the way do go afterall.

    Thanks again,

    Steve

  6. #6
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Yes, you can do this in any version of sql server as long as you have a unique column (or columns) that you can order by.
    Code:
    select	MyTable.*,
    	(select	count(*)
    	from	MyTable SubTable
    	where	SubTable.SortColumn <= MyTable.SortColumn) as OrdinalValue
    from	MyTable
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •