• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Tim Cooke
  • paul wheaton
  • Paul Clapham
  • Ron McLeod
Sheriffs:
  • Jeanne Boyarsky
  • Liutauras Vilda
Saloon Keepers:
  • Tim Holloway
  • Carey Brown
  • Roland Mueller
  • Piet Souris
Bartenders:

Selecting random rows

 
Ranch Hand
Posts: 113
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
I need to select 4 random rows from a table that contains approximatly 72,000 rows.
Is there a way to do it efficently without making the code database dependant.
If not then Im using Oracle 8.1.7
My first thought was to do it this way:
select everything
go to the last row and get the row number n
generate four random numbers from 0 to n
retrieve the records at those locations
I would like to somehow select 4 random rows instead of everything. There are no rows in the table that can be used as keys for this.
Thanks in advance.
Steve
 
Ranch Hand
Posts: 1879
MySQL Database Suse
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
can you add a column to the table to have have one column that is a number generated by a sequence starting at 1? If so, you can just use "select count(*) from table". create the random numbers between between 1 and the returned count. Then do a "select ... from table where new_column = " + random_number1 + " OR new_column = " random_number2 + .....
Otherwise, you could do a "select count(*) from table" to find the number of rows in the table. Again, generate 4 random numbers in java between 1 and the row count. sort the random numbers in ascending order ( 1, 2, 3....). Loop through the resultset retreiving only the rows that are equal to the random numbers
something like:

as for any easy tricks, I don't know of any.
Jamie
[ June 17, 2002: Message edited by: Jamie Robertson ]
[ June 17, 2002: Message edited by: Jamie Robertson ]
 
whippersnapper
Posts: 1843
5
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

Originally posted by Jamie Robertson:
can you add a column to the table to have have one column that is a number generated by a sequence starting at 1? If so, you can just use "select count(*) from table". create the random numbers between between 1 and the returned count. Then do a "select ... from table where new_column = " + random_number1 + " OR new_column = " random_number2 + .....


Note that if you're working in Oracle and you don't have the luxury of adding the column Jamie describes, there is a workaround. You can fake a row number to qualify on by using the Oracle pseudocolumn rownum and an inline view.
The typical use of rownum is to limit the number of rows returned by a query by an arbitrary number of rows (however the results may or may not be sorted). Note that rownum is generated by the query and does not purport to represent how the underlying rows may be stored on the table. (Read the docs on rownum to understand the interaction between rownum and order by.) The following query returns the first 5 rows generated by the query.
select rownum , e.* from etable e where rownum < 6 ;
Note that you cannot use rownum with = (or > to pick a row based on rownum
select e.* from etable e where rownum = 3 ; /* Returns zero rows */
because Oracle fetches a row, assigns it rownum 1, that row fails the condition of rownum = 3 and is discarded. Another row it fetched and assigned rownum 1, fails the condition and is discarded, etc.
UNLESS you generate the rownum as an inline view and qualify on the aliased column in the enclosing select:
select mm
from (
select rownum mm
from etable e )
where mm = 3

Note that to actually get the fields from the inline view, you seem to need to alias them individually:
select mm , field1 , field2 , field3
from (
select rownum mm ,
e.field1 field1 ,
e.field2 field2 ,
e.field3 field3
from etable e )
where mm = 3

Now instead of qualifying on mm = 3, you'd qualify on the random numbers you generated in the calling routine like Jamie mentioned.
[ June 17, 2002: Message edited by: Michael Matola ]
 
Consider Paul's rocket mass heater.
reply
    Bookmark Topic Watch Topic
  • New Topic