Results 1 to 6 of 6
  1. #1
    Join Date
    Aug 2002
    Posts
    121

    Unanswered: SELECTing "X" rows

    Hello,
    I want to run a query that will select every 10th row from a table. I'd prefer not to use a cusrsor to do this if possible.


    Thanks

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    see if this helps --
    Selecting every 100th of 1,000,000 rows

    a cursor would be the easiest

    in oracle, i believe you can use rownum, and do a MOD function on it


    rudy
    http://r937.com/

  3. #3
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: SELECTing "X" rows

    Originally posted by tmalone
    Hello,
    I want to run a query that will select every 10th row from a table. I'd prefer not to use a cusrsor to do this if possible.


    Thanks
    select * from t sample (10);

    That will return 10% of the rows in t

  4. #4
    Join Date
    Aug 2002
    Posts
    121
    Thanks rudy,
    Yes, I've done it with a cursor already, using the MOD function and the
    %ROWCOUNT attribute.

    I just thought there might have been an innovative way of doing it with a SELECT statement, without having to resort to a cursor.

    I appreciate the quick respose. Thanks

    TM

  5. #5
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Originally posted by tmalone
    Thanks rudy,
    Yes, I've done it with a cursor already, using the MOD function and the
    %ROWCOUNT attribute.

    I just thought there might have been an innovative way of doing it with a SELECT statement, without having to resort to a cursor.

    I appreciate the quick respose. Thanks

    TM
    If the SAMPLE isn't good enough, i.e. if you MUST get rows 10,20,30,... then you can do this:

    Code:
    select ename
    from
    ( select ename, rownum rn
      from
      ( select ename
        from emp
        order by ename
      )
    )
    where mod(rn,10) = 0
    And yes, it does require 3 levels of nested selects if the data is to be ordered!

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    tony, thanks for mentioning oracle's SAMPLE -- another arrow in my quiver

    wish i'd known it when i wrote that article, eh

    rudy

Posting Permissions

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