Results 1 to 14 of 14

Thread: number sequence

  1. #1
    Join Date
    Sep 2003
    Location
    New York, NY
    Posts
    136

    Question Unanswered: number sequence

    is it possible to generate a number sequence in a query (without using loop). I want the output to look as
    -------
    ID
    -------
    1
    2
    3
    4
    5
    6
    7
    8
    9
    ....
    upto the last number I give in the query

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Ok, I'll bite...

    ...Why?!
    George
    Home | Blog

  3. #3
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Sure:
    Code:
    SELECT n0 + n1 + n2 + n3 + n4 + n5
       FROM (SELECT 0 AS n0 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3) AS z0
       CROSS JOIN (SELECT 0 AS n1 UNION SELECT 4 UNION SELECT 8 UNION SELECT 12) AS z1
       CROSS JOIN (SELECT 0 AS n2 UNION SELECT 16 UNION SELECT 32 UNION SELECT 48) AS z2
       CROSS JOIN (SELECT 0 AS n3 UNION SELECT 64 UNION SELECT 128 UNION SELECT 192) AS z3
       CROSS JOIN (SELECT 0 AS n4 UNION SELECT 256 UNION SELECT 512 UNION SELECT 768) AS z4
       CROSS JOIN (SELECT 0 AS n5 UNION SELECT 1024 UNION SELECT 2048 UNION SELECT 3072) AS z5
       ORDER BY 1
    -PatP

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    What's your SQL Server version?

  5. #5
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by georgev
    ...Why?!
    They're looking for a numbers table.

    -PatP

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by Pat Phelan
    They're looking for a numbers table.
    Bet you $4.84 they aren't.

  7. #7
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    I'll bet him $4.85 they aren't either
    George
    Home | Blog

  8. #8
    Join Date
    Sep 2003
    Location
    New York, NY
    Posts
    136
    SQL Server 2005, thats what I was looking for Pat, thank you.

    do I get a cut from the bet?

  9. #9
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by rohitkumar
    do I get a cut from the bet?
    Sounds like a dodgy deal to me George. I'm calling the feds

  10. #10
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313
    select number
    from master.dbo.spt_values
    where type='P' and number between 1 and 9


  11. #11
    Join Date
    Nov 2004
    Location
    Canada
    Posts
    58
    There is a max number limit to using the number field from spt_values, different in 2000 and 2005. If you need numbers past what it can hold here is a suggestion.

    create a table with identity column using an existing table with the number of rows greater than what you are looking for. (find your largest table)


    SELECT ROWID=IDENTITY(int,1,1)
    INTO NUMBERS1 FROM MYALREADYEXISTINGTABLE

    SELECT ROWID FROM NUMBERS1
    ORDER BY ROWID
    where rowid <1000

  12. #12
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I know that you said "no looping", but play with this one for a minute or two when you have a chance.
    Code:
    CREATE TABLE numbers (
       number			INT			NOT NULL
       CONSTRAINT XPKnumbers
          PRIMARY KEY CLUSTERED (number)
    	)
    
    INSERT INTO numbers (number) VALUES (0)
    
    DECLARE @i			INT
    SET @i = 20
    
    WHILE 0 < @i
       BEGIN
          INSERT INTO numbers (number)
             SELECT number + (SELECT 1 + Max(number) FROM numbers)
                FROM numbers
    
          SET @i = @i - 1
       END
    
    SELECT * FROM numbers
    -PatP

  13. #13
    Join Date
    Nov 2005
    Posts
    122

  14. #14
    Join Date
    Jul 2007
    Posts
    96
    -- Nevermind my reply. Misread the question. I was suggesting to use Row_Number() if the intention was to append such column on an existing Dataset :P --
    Last edited by Diabolic; 01-13-08 at 08:00.

Posting Permissions

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