Generate random rows using SQL

I often get asked how to generate random rows in SQL without having to create a table with required number of rows.  For example, how can we easily print out the first 100 numbers without first creating a table with atleast 100 rows.

And the answer to the above question is – you use the ‘CONNECT BY’ clause with rownum, usually against the dual table.

And how exactly do we do that? Well here’s the SQL for it.

SELECT rownum
FROM dual
CONNECT BY rownum < 101;

The above SQL prints out the first 100 numbers. Each row selected has the rownum value assigned to it, which is then compared to the number 101 and if the rownum is less, you move on to get the next row. You stop when you reach a rownum of 101, since the condition is no longer satisfied. 

The above was a very simple case.  Let’s go slightly more complex.  I am now going to show you how to generate days of the month for the whole year using just SQL and the technique above.

To start off with, we try and get the months of the year generated using the SQL below :

(SELECT mnth, last_day(mnth) – mnth + 1 num_days, rnum
FROM
  (SELECT to_date(rownum, ‘MM’) mnth, rownum rnum
   FROM dual
   CONNECT BY rownum < 13))

This gives you the output shown below:

“MNTH”                        “NUM_DAYS”        “RNUM”                      
“01-JAN-10”                   “31”                          “1”                         
“01-FEB-10”                    “28”                          “2”                         
“01-MAR-10”                  “31”                          “3”                         
“01-APR-10”                   “30”                          “4”                         
“01-MAY-10”                  “31”                          “5”                         
“01-JUN-10”                    “30”                          “6”                         
“01-JUL-10”                     “31”                          “7”                         
“01-AUG-10”                    “31”                          “8”                         
“01-SEP-10”                      “30”                          “9”                         
“01-OCT-10”                     “31”                          “10”                        
“01-NOV-10”                    “30”                          “11”                        
“01-DEC-10”                     “31”                          “12”                        

Basically we are getting 12 rows selected by the inner query and then using the to_date and last_day functions to generate the first day of each month in the year and a count of the number of days per month. 

Now to get the days of any month, say January, we can use this query

SELECT rownum dy
FROM dual
CONNECT BY rownum < 32;

(You can also use CONNECT BY rownum <= 31 above of course)

The above query gives me 31 rows as required. So now, we’ve got the queries for the months of the year and the days of a month.  We will now put it all together and generate days for all the months in the year.

WITH
month_view as
(SELECT mnth, last_day(mnth) – mnth + 1 num_days, rnum
FROM
  (SELECT to_date(rownum, ‘MM’) mnth, rownum rnum
   FROM dual
   CONNECT BY rownum < 13)),
day_view as
(SELECT rownum dy
FROM dual
CONNECT BY rownum < 32)
SELECT a1.mnth + a2.dy – 1 current_day, a1.mnth, a1.num_days from month_view a1, day_view a2
where a1.num_days >= a2.dy;

This gives you the required result (a subset is shown below)

“CURRENT_DAY”                 “MNTH”                        “NUM_DAYS”                  
“01-JAN-10 ”                        “01-JAN-10”                     “31”                        
“02-JAN-10 ”                        “01-JAN-10”                     “31”                        
“03-JAN-10 ”                        “01-JAN-10”                     “31”                        
“04-JAN-10 ”                        “01-JAN-10”                     “31”                        
“05-JAN-10 ”                        “01-JAN-10”                     “31”                        
“06-JAN-10 ”                        “01-JAN-10”                     “31”                        
“07-JAN-10 ”                        “01-JAN-10”                     “31”                        
“08-JAN-10 ”                        “01-JAN-10”                      “31”                        
“09-JAN-10 ”                        “01-JAN-10”                      “31”                        
“10-JAN-10 ”                        “01-JAN-10 ”                           “31”