DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: Unexpected results using rownum

  1. #1
    Join Date
    Dec 1999
    Location
    Purgatory
    Posts
    346

    Unexpected results using rownum

    I'm trying to return just 100 rows from a table with the following code......
    Code:
    select membership_number, min(response_date)
      from question_response_001
         where response_date 
                  between to_date('01/04/2004','dd/mm/yyyy') and 
                              to_date('30/06/2004','dd/mm/yyyy')
         and rownum < 101
         group by membership_number
    )
    but I keep getting different result sets returned, i.e., 100 rows, 97 rows, 86 rows.

    Is the 'group by' somehow affecting this??

  2. #2
    Join Date
    May 2005
    Location
    Toronto Canada
    Posts
    57
    The query is effectively finding the first 100 rows that match the criteria, then, from that resultset, doing the GROUP BY and MIN.

    I would expect that if you ran that exact query over and over, it would return the same results each time (at least today, tomorrow may be different), however I would not necessarily expect 100 rows returned, If you change the date range, I would not be surprised to see a different number of rows returned every time.

    HTH
    John

  3. #3
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    you need to order by and then reduce by rownum. for example

    Code:
    select * from
       (Select membership_number, min(response_date) resp_date
        from question_response_001
        where response_date 
                  between to_date('01/04/2004','dd/mm/yyyy') and 
                              to_date('30/06/2004','dd/mm/yyyy')
         group by membership_number
         order by resp_date desc
    )
    where rownum < 101
    Last edited by marist89; 08-09-2005 at 11:32 AM. Reason: code formatting
    Jeff Hunter

  4. #4
    Join Date
    Dec 1999
    Location
    Purgatory
    Posts
    346
    Thanks for the advice,

    The fundamental issue was that membership_number was not unique (not primary key). So the query returns 100 rows, some with the same membership_number. The 'group by' will then reduce the number of rows where a duplicate membership_number is found.

Posting Permissions

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


Click Here to Expand Forum to Full Width