Results 1 to 2 of 2
  1. #1
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4

    Unanswered: sequential number in a view

    Hi,

    We are integrating all our applications/databases into one application/database. During the transition phase, I need to create a number of views based on the new database that mimic the old tables of the old databases, so the old programs can continue to function until they are gradually replaced.

    In one of the views, I need to generate a sequential number. The value is unimportant, as long as it is unique in the dataset; strictly spoken, it even doesn't need to be sequential:

    eg:
    SELECT * FROM myView
    should give
    Code:
    col1	col2	...	id
    lala	car	..	1
    baba	bike	..	2
    ....
    zsrd	pen	..	896
    ghrt	ink	..	897
    SELECT * FROM myView ORDER BY col2
    should give
    Code:
    col1	col2	...	id
    baba	bike	..	1
    lala	car	..	2
    ..
    ghrt	ink	..	45
    ..
    zsrd	pen	..	396
    ....
    The view is created based on a number of tables.
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  2. #2
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    I came up with a solution.

    Code:
    CREATE VIEW myView AS
    WITH CTE AS
    (SELECT col_1, col_2, ....,
            ROW_NUMBER() OVER (ORDER BY col_v, col_w) as id
    FROM table1
            INNER JOIN table2 ON
                  table1.col_p = tabme2.col_q
            INNER JOIN table3 ....
    ....
    )
    SELECT col_1, col_2, ... , col_x, id
    FROM CTE
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

Posting Permissions

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