Selecting a Random Set of Rows from a Table

[id10t]

For exact 3 colours or N colours use in query of @doug_harmon:

Code: Select all

truncate table ColorsRand; 
insert into ColorsRand select subquery.colour from (select random() + randomint(100), colour from Colors order by 1) as subquery limit 3; 
select * from Colors left outer join ColorsRand on Colors.colour = ColorsRand.colour;

Code: Select all

TRUNCATE TABLE
 OUTPUT 
--------
      3
(1 row)

 colour  | colour  
---------+---------
 #DCDCDC | #DCDCDC
 #FFCCBB | 
 Black   | 
 Blue    | 
 Green   | 
 Magenta | Magenta
 Orange  | 
 White   | 
 Yellow  | Yellow
(9 rows)

[JimKnicely]

insert into ColorsRand select subquery.colour from (select random() + randomint(100), colour from Colors order by 1) as subquery limit 3;

If you need to do this on a larger scale, you can change the subquery just a tad so that you won't need to call the random() function...

Code: Select all

insert into ColorsRand select subquery.colour from (select colour from Colors order by randomint(100)) as subquery limit 3;

What do you guys think?

PS. Oracle has a nice SAMPLE() function that can be used to grab random rows from a table... Maybe Vertica will implement a similar feature in the future?