Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Join Date
    Mar 2012
    Posts
    16

    Unanswered: Generating a list of sequential numbers

    Hi everyone,

    I've been working with MySQL for a while and understand the basics. I'm trying to do something that should be pretty simple but turns out to be pretty hard.

    I spent a bunch of time creating a query that creates a sum of a whole bunch of different fields. Call that sum X, and it could theoretically be very large. X is different for all of my users. I just want to alter my query a little bit further to generate a temporary list of sequential numbers from 0 to X to use as in a dynamic drop-down menu. I cannot find a way to do this.

    What I did find was something like this:

    Code:
    SELECT
        (@row:=@row+1) AS row
    FROM table,
    (SELECT @row:=0) AS row_count;
    But the problems are:
    1. You have to select a specific table to use and I don't have a table with enough rows for some of the larger X values.
    2. It only seems to be able to generate the exact number of columns in the table, and I can't figure out how to limit it by the user's X value. I tried a hypothetical X value of 50 using a table of 100 rows with the following:

    Code:
    SELECT
        (@row:=@row+1) AS row
    FROM table,
    (SELECT @row:=0) AS row_count
    SELECT
        (@row:=@row+1) AS row
    FROM table,
    (SELECT @row:=0) AS row_count
    Where row<50
    but of course I received a message that "row" was an unknown column.

    If anyone can help, I'd really appreciate it. This has frustrated me for the better part of 2 days.

    Thanks!

    -CB

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    George
    Home | Blog

  3. #3
    Join Date
    Mar 2012
    Posts
    16
    Thanks, but no. In that case, they just want to create numbers for the rows of a table. In my case, I have a variable number that isn't associated with a table, its a sum of a bunch of different values. I want a count of every integer up to that sum.

    For example, X could be 10 for user 1. I want a return of 0, 1, 2, 3, 4, 5, 6, 7, 8, 9, and 10. X could be 1050 for someone else. I'd like a return of 0, 1, 2,...1050.

    -CB

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Are you sure you should be doing this in MySQL and not your front end language?

    If you're determined then the simplest solution would be a numbers table.
    See the following thread (pay particular attention to Pats first reply for some workable code):

    http://www.dbforums.com/microsoft-sq...-sequence.html

    Use the query to populate a numbers table (yep, a table with a single integer column that's also the primary key) then you can join to it and limit the query using the your value as required.
    George
    Home | Blog

  5. #5
    Join Date
    Mar 2012
    Posts
    16
    Ok thanks for the reality check, I can very easily generate this list of numbers using the range() function in php. I can't believe that function doesn't exist in mysql!

    Now, the only remaining problem is populating a drop-down with the dynamic values. That is why I wanted to do it in SQL to begin with, I know how to get the dynamic values from an SQL query into a drop down using dreamweaver. I'm going to look into how to do that.

    Thanks so much!

    PS you guys are FAST!

    -CB
    Last edited by cblockus; 03-10-12 at 23:36.

  6. #6
    Join Date
    Mar 2012
    Posts
    16
    OK wanted to try the number table idea. I used the query

    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
    copied from the link you provided. It works fine in navicat, but then I tried to use it in the SQL editor in dreamweaver and I got:

    MySQL Error#: 1064

    You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ') AS z0 CROSS JOIN (SELECT 0 AS n1 UNION SELECT 4 UNION SELECT 8 UNION SELECT' at line 1

    Any ideas?

    Thanks!

    -CB

  7. #7
    Join Date
    Sep 2009
    Location
    San Sebastian, Spain
    Posts
    880
    Have you tried something like this:

    SET @row := 0;
    SELECT row
    FROM (SELECT @row := @row + 1 as row FROM table) AS x
    WHERE row < 50;
    Ronan Cashell
    Certified Oracle DBA/Certified MySQL Expert (DBA & Cluster DBA)
    http://www.it-iss.com
    Follow me on Twitter

  8. #8
    Join Date
    Mar 2012
    Posts
    16
    Thanks for the reply Ronan,

    Your query works great, but I don't have a big enough table for what I need. I guess I could just make a table with a million rows, but I wish there was a more elegant way to do it. I might just use the php range function, I would have just preferred to do it all in sql.

  9. #9
    Join Date
    Mar 2012
    Posts
    16

    slightly different direction

    I had a thought about this. I have compiled my sum, which is different for each person, but I need to do the same thing with it each time. My problem is that I don't have a table big enough for some of the larger sums, and I'm not terribly fond of the "create a permanent table with a zillion rows" hack. I've been reading about creating temporary tables, and I was wondering how simple it would be to run a query that would read something like:

    Create a temporary table with the column 'number' and X rows, where X is the user's sum, populate them with auto-incremented numbers, then select all of those temporary rows. Now, drop the table.

    I've never worked with temporary tables before. Is this a train of thought worth pursuing?

  10. #10
    Join Date
    Mar 2012
    Posts
    16
    Actually nevermind, I did this the ugly way (with PHP). Thanks though!

    -CB

  11. #11
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Creating a permanent numbers table is actually a great idea - they come in handy so often! However I haven't ever created one with anywhere near a million rows (I think my standard one is around 64K rows and I have never got close to using that limit).

    If that query works in one environment (i.e. not in Dreamweaver) then use it to populate a table using an INSERT statement like so
    Code:
    INSERT INTO dbo.numbers (number)
    SELECT n0 + n1 + n2 + n3 + n4 + n5
    FROM   ...
    But yes, I think in this instance you should be doing the legwork in PHP
    George
    Home | Blog

  12. #12
    Join Date
    Mar 2012
    Posts
    16
    Ok I'm back to trying to generate a numbers table in mysql. I've tried using this code to create the table with no success:

    `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`

    I get #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'CONSTRAINT XPKnumbers PRIMARY KEY CLUSTERED (number) ) INSERT INTO n' at line 3

    Any suggestions for creating a numbers table in mysql?

  13. #13
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    you forgot the comma
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  14. #14
    Join Date
    Mar 2012
    Posts
    16
    where?

    -CB

  15. #15
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    um...

    here --
    #1064 near 'CONSTRAINT
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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