Results 1 to 5 of 5

Thread: Query Please

  1. #1
    Join Date
    Jul 2003
    Posts
    70

    Unanswered: Query Please

    Hello All,

    Can anyone tell me how to write a query which returns a random single record everytime from a table with more than 10000 records.

    Please Help

    qAnand

  2. #2
    Join Date
    Jan 2004
    Location
    India
    Posts
    62
    Is there any critiria by the way for the record ?
    If not you can just write something like
    select * from table_name
    where rownum = to_number(to_char(sysdate, 'ss'));
    this will compare the row number with current sec. and every time you will get a different record.
    Are you looking for this OR something else ?
    Regards,

    Rushi

  3. #3
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    http://download-west.oracle.com/docs...3a.htm#2066411

    Selecting a Sample: Examples
    The following query estimates the number of orders in the oe.orders table:

    SELECT COUNT(*) * 100 FROM orders SAMPLE (1);


    The following example creates a sampled subset of the sample table hr.employees table and then joins the resulting sampled table with departments. This operation circumvents the restriction that you cannot specify the sample_clause in join queries:

    CREATE TABLE sample_emp AS
    SELECT employee_id, department_id FROM employees SAMPLE(10);

    SELECT e.employee_id FROM sample_emp e, departments d
    WHERE e.department_id = d.department_id
    AND d.department_name = 'Sales';

  5. #5
    Join Date
    Jan 2004
    Location
    Leiden, The Netherlands
    Posts
    11
    To Rushi

    I don't believe that

    select * from table_name
    where rownum = to_number(to_char(sysdate, 'ss'));

    will work.

    It doesn't work in Oracle 7.3 or Oracle 8.1

    You can do

    select * from table_name
    where rownum <= to_number(to_char(sysdate, 'ss'));

    but you will get almost always more than 1 row.

    You can't do

    select * from table_name
    where rownum = 10;

Posting Permissions

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