-
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??
-
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
-
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
-
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|