2

I want to know if it's possible to generate more than 1 row on this select statement:

select
      floor(1+(rand()*(1+100-1))) as B1
     ,floor(1+(rand()*(1+100-1))) as B2
     ,floor(1+(rand()*(1+100-1))) as B3
     ,floor(1+(rand()*(1+100-1))) as B4
     ,floor(1+(rand()*(1+100-1))) as B5
     ,floor(1+(rand()*(1+100-1))) as B6 ;

instead of this ( single row)

    B1  B2  B3  B4  B5  B6
    -- --   --  --  --  --
    48  35  30  44  31  24

I would like to see this ( or many rows as I want like in a loop or while)

B1  B2  B3  B4  B5  B6
-- --   --  --  --  --
48  35  30  44  31  24
24  2   34  15  22  15
11  7   2   36  27  26
49  19  44  17  49  47
39  4   48  32  16  34
23  10  32  29  48  9
45  49  13  17  45  25
38  16  15  25  33  41

If anyone knows if is it possible to do that without creating procedures I would appreciate !

Haohmaru
  • 568
  • 1
  • 7
  • 15
  • 3
    no. you'd have to change to multiple selects and `union`. e.g. `select 'a' union all select 'b' union all .... select 'z'`. – Marc B Apr 16 '15 at 17:20
  • @MarcB Why not post this as an answer? – leoger Apr 16 '15 at 17:42
  • Can we use an existing database table in our answer? In other words, can we query a built-in mysql table? – Mark Leiber Apr 16 '15 at 17:47
  • @MarcB you're thoughtful, I appreciate your suggestion it works.. why this haven't come to my mind before... Thank you everyone for the time and appreciation. – Haohmaru Apr 17 '15 at 10:53

2 Answers2

5

Most databases provide a way to generate 1..n rows of data (see: SQL SELECT to get the first N positive integers) but MySQL doesn't make it easy. If you have a table that you know has enough rows to meet your requirements you can use that as a basis for a query to get what you want.

For example this will get you 10 rows:

SELECT @N := @N +1 AS rownumber
     ,floor(1+(rand()*(1+100-1))) as B1
     ,floor(1+(rand()*(1+100-1))) as B2
     ,floor(1+(rand()*(1+100-1))) as B3
     ,floor(1+(rand()*(1+100-1))) as B4
     ,floor(1+(rand()*(1+100-1))) as B5
     ,floor(1+(rand()*(1+100-1))) as B6 
FROM INFORMATION_SCHEMA.COLUMNS, (SELECT @N:=0) dummyRowNums LIMIT 10;

You can use any table to achieve this as long as you can be sure it has more rows than the LIMIT you're looking to reach.

If you don't need a rownumber in your result set, you can remove the first column in the SELECT and can remove the join (", (SELECT @N:=0) dummyRowNums"):

SELECT floor(1+(rand()*(1+100-1))) as B1
     ,floor(1+(rand()*(1+100-1))) as B2
     ,floor(1+(rand()*(1+100-1))) as B3
     ,floor(1+(rand()*(1+100-1))) as B4
     ,floor(1+(rand()*(1+100-1))) as B5
     ,floor(1+(rand()*(1+100-1))) as B6 
FROM INFORMATION_SCHEMA.COLUMNS LIMIT 10;
Community
  • 1
  • 1
Mark Leiber
  • 3,118
  • 2
  • 13
  • 22
  • What is the purpose of `(SELECT @N:=0)` here? – Andriy M Apr 16 '15 at 18:20
  • @AndriyM I updated the answer to clarify. You only need that if you want to get a row number back in your results. It's just a dummy value that is incremented. – Mark Leiber Apr 16 '15 at 18:25
  • Right, I just wasn't sure whether you forgot to include `@N+1` or you forgot to remove the subquery. I think the row numbering thing may be a useful addition, but at the same time I think it's redundant to your solution, as the OP never asked for it. It might even become more of a distraction for them (in fact, for anyone who is more or less still a newbie) in their attempt to understand how your answer works. – Andriy M Apr 16 '15 at 18:44
  • @AndriyM: fair enough. I've updated the response with the exact query to be used if the row number is not needed. Thanks. – Mark Leiber Apr 16 '15 at 18:48
1

For SQL Server, use a recursive CTE... and mind your seeds for the RAND function.

;With integers( num ) as
(
    Select 1 as num
        union all
    Select num + 1
        from integers
        where num <= 10
)
select
      num,
      floor(1+(rand(CHECKSUM(NEWID()))*(1+100-1))) as B1
     ,floor(1+(rand(CHECKSUM(NEWID()))*(1+100-1))) as B2
     ,floor(1+(rand(CHECKSUM(NEWID()))*(1+100-1))) as B3
     ,floor(1+(rand(CHECKSUM(NEWID()))*(1+100-1))) as B4
     ,floor(1+(rand(CHECKSUM(NEWID()))*(1+100-1))) as B5
     ,floor(1+(rand(CHECKSUM(NEWID()))*(1+100-1))) as B6 
    from integers

See this question for the RAND seed issue: RAND not different for every row in T-SQL UPDATE

Community
  • 1
  • 1
Brett Green
  • 3,535
  • 1
  • 22
  • 29