Results 1 to 5 of 5
  1. #1
    Join Date
    Feb 2003
    Posts
    7

    Angry Unanswered: Counting problem

    Hello DB experts!
    I have a problem ...
    I need to count a number of records and than stop the query (if the amount of records exceeds my max number)
    I tried doing it using ROWNUM<300 but I need to use DISTINCT in the query, so after 300 iterations the query does stop, but it returns less than 300 records count (due to the DISTINCT)...
    My question is - how can I stop the search exactly after 300 records with the DISTINCT ?


    Thank you !

  2. #2
    Join Date
    Jan 2003
    Posts
    18
    Hi,
    I will tried out ur case with one small table "emp1".

    select * from emp1;
    NO NAME
    --- ----
    1 ssk
    1 ssk
    1 ssk
    2 abc
    2 abc
    3 s
    3 s
    3 s

    when query the table like
    select distinct no,name from emp1 where rownum <4;

    NO NAME
    --- -----
    1 ssk

    but ur expectation was to get 3 distinct rows...
    so try to query the table like...
    select * from (select distinct no,name from emp1) where rownum <4;

    NO NAME
    ----- -----------
    1 ssk
    2 abc
    3 s

    Hope this will solve your problem.

    Cheers,
    Senthil

  3. #3
    Join Date
    Feb 2003
    Posts
    7

    Count problem

    Thanks, but...
    The basic reason that I needed using ROWNUM is that the table is very large and I want to avoid runing all over it.
    This solution is great except for that reason,
    Is there any way that I could still limit the search for 300 records without scanning the whole table ?

  4. #4
    Join Date
    Jan 2003
    Posts
    18

    Re: Count problem

    I will think over...If I get any solution
    I will let u know.
    Regards,
    Senthil

    Originally posted by yarivp
    Thanks, but...
    The basic reason that I needed using ROWNUM is that the table is very large and I want to avoid runing all over it.
    This solution is great except for that reason,
    Is there any way that I could still limit the search for 300 records without scanning the whole table ?

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

    Re: Count problem

    Originally posted by yarivp
    Thanks, but...
    The basic reason that I needed using ROWNUM is that the table is very large and I want to avoid runing all over it.
    This solution is great except for that reason,
    Is there any way that I could still limit the search for 300 records without scanning the whole table ?
    Unfortunately, you can't do that. Without "running all over" the table, you cannot identify the DISTINCT values that it contains. The optimiser may choose to do a full scan of an index rather than the table if it can, that's about all.

Posting Permissions

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